Software

Ask something
Question Locked

Excel Help

0 helpful
4 replies

by benpatel

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have created a work book to log faults on CCTV cameras from multiple sites. The problem i have got is what formula to use to show the location of the camera. Please see example below.

A B C
Cam Site Cam No Cam Location
1 Store A 2 Right Corner
2 Store B 1 Manin Entrance
3 Store A 8 Back YArd
4 Store C 3 Corridor A

Each store has 200 cameras and the are 10 stores. The Camera log needs to be able to log 200 faults. At the moment i have all the cameras logged on a sheet in the following format,

Store Name Camera Number Camera Location

so i have 2000 entries in total.

any help would be greatly recieved.

many thanks in advance!

Ben

4 replies

by thumbsup2

... to manually assign a unique ID to each of the cameras listed in Sheet1 (after you've sorted it), then change your logging routine in Sheet2 to include the ID of the camera you're inputting and put a VLoookup function in the Camera Location column. This ID can easily be added in a column to the right of your existing data on Sheet1 so you won't mess up the column location of the other data.

Without doing that, you'll need to figure out how to 'lookup' the Camera Location based on the contents of TWO fields, the Store Name (Cam Site) and the Camera Number (Cam No), which necessitates a compound (complex) VLookup formula.

by benpatel

What if i named the range of cameras for each store ( so there would be 10 ranges, is there a way displaying a range depending on another cells contents?

by thumbsup2

If you write a script, yes there is a way to 'display' a range ... but using strictly formulas, no, you can not.

This discussion is closed to new replies.