Excel Help

By benpatel ·
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.

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!


This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

The easiest solution would be...

by ThumbsUp2 In reply to Excel Help

... 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.

Collapse -

What if..

by benpatel In reply to The easiest solution woul ...

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?

Collapse -

Not using what you're using.....

by ThumbsUp2 In reply to What if..

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

Related Discussions

Related Forums