Greetings -
I have no problems index matching from one worksheet so that the desired output posts in a column cell in another worksheet. What I haven't been able to figure out yet is how to write a formula to check more than one worksheet for the same value.
Here's my scenario:
=IFERROR(INDEX('[Site 1]'!M$14:M$100,MATCH(C6('[Site 24]'!C$14:C$100,0)),"")
Let's assume that Device 0001 is not at Site 1 but is at Site 2, which I'll already know because it's identified in another column called "Current Location". However, the formula doesn't know that so I want it to search for Device 0001 in both worksheets (identical columns) until it locates it. If Device 0001 isn't located at either site then I want the cell to return blank.
So, how would I alter the above formula so that it searches more than one worksheet. Ultimately, I need it to have the capability to search all 16 worksheets (or site locations). Again, the device will only be found in ONE worksheet/site location at a given time.
Many thanks ahead of time for the help and expertise...
Tom
I have no problems index matching from one worksheet so that the desired output posts in a column cell in another worksheet. What I haven't been able to figure out yet is how to write a formula to check more than one worksheet for the same value.
Here's my scenario:
- I'm trying to locate a single device with a unique serial number, which I will call "Device 0001"
- Device 0001 can be located at any one of 16 different site locations at a given time but only at ONE location at a time
- My workbook contains 16 worksheets (one for each site location) and EACH worksheet is identical. In other words, the same columns (e.g. Column M and Column C) in each worksheet are searched.
- When Device 0001 is located, I want the formula to tell me its status at the site location
=IFERROR(INDEX('[Site 1]'!M$14:M$100,MATCH(C6('[Site 24]'!C$14:C$100,0)),"")
Let's assume that Device 0001 is not at Site 1 but is at Site 2, which I'll already know because it's identified in another column called "Current Location". However, the formula doesn't know that so I want it to search for Device 0001 in both worksheets (identical columns) until it locates it. If Device 0001 isn't located at either site then I want the cell to return blank.
So, how would I alter the above formula so that it searches more than one worksheet. Ultimately, I need it to have the capability to search all 16 worksheets (or site locations). Again, the device will only be found in ONE worksheet/site location at a given time.
Many thanks ahead of time for the help and expertise...
Tom