Thread: How do I get information from multiple sheets into my Master sheet?

Hi there.

Basically..

In a folder there are four other folders with their locations. The locations are Sheffield, Barnet, Norfolk and Cornwall.

Each have client numbers depending on their location.

In Cornwall, the number starts with C400
In Norfolk, the number starts with N400
In Sheffield, the number starts with S400
In Barnet, the number starts with B400

In the master document I have all of my client numbers listed and would like to be able to use a formula to look in all of the sheets at the same time and perform a function. Because the beginning letter will only appear in that spreadsheet there is no chance that the formula will come across two of the same ID numbers.

I tried using the =INDEX(MATCH, MATCH, 0) formula, but I couldn't find a way for it to look in more than one spread sheet to operate.

The layout in all the sheets is also identical so INDEX would work but.. so far.. to no avail

Any help is greatly appreciated.

Adam.

Originally Posted by NBVC

What you can do is extract the name of the city (name of sheet) from the client number first, then use that to go to relevant sheet.

So if your list is in Column A, then in column B, enter:

=LOOKUP(LEFT(A2),{"B","C","N","S"},{"Barnet","Cornwall","Norfolk","Sheffield"})

assuming starting in row 2....

Then in column C apply your Index function with indirect references to the proper sheet....

e.g.

=INDEX(INDIRECT("'"&B2&"'!A:A"),MATCH(A2,INDIRECT("'"&B2&"'!B:B"),0))

this looks up code in A2 in the column B of the sheet with appropriate name and returns from column A.

Hi there,

Thanks for the reply. I am just trying to wrap my head around this new concept.

I am a little confused on what the second formula is doing. I understand the first is is giving a name based on a single letter. But the second one is so far eluding me.

The problem is, is that the workbooks are all in different folders.

So Norfolk is in:

C:\Documents and Settings\aosgood\Desktop\INDEX PRACTICE\Layer1\Layer2\Norfolk\[Norfolk.xlsx]Sheet1'!$A$2:$A$4

and Barnet is in:

C:\Documents and Settings\aosgood\Desktop\INDEX PRACTICE\Layer1\Layer2\Barnet\[Barnet.xlsx]Sheet1'!$A$2:$A$4

and as far as I can tell neither of the formulas would be actively searching for these files, maybe I wasn't clear and you thought they were in the same workbook? Sorry about that, and I know this is a lot to ask but if you could create an example spreadsheet for me and just show me exactly how these formulas would be working, hopefully I would be able to start from there and modify it when needs be!

Thanks very much!

Adam

Long time no speak!

I have been trying to get your plan to work but until now I just can't do it. I downloaded the add on as you suggested and I tried doing as you said but it didn't work. I then changed the answer given in the first formula as the location of my file, instead of just Barnet it was c:\documents etc etc.

I thought that would get it working but it didn't. if you could literally type it into a blank sheet with the formula taking information from the right cells then I can probably figure it out from there.. I hope :D

Thanks

Originally Posted by NBVC

How did it not work.. wrong results...

I noticed that I hadn't put a ,0 in my Match() function to define exact matching...

=INDEX(INDIRECT.EXT("'C:\Documents and Settings\aosgood\Desktop\INDEX PRACTICE\Layer1\Layer2\"&B2&"\["&B2&".xlsx]Sheet1'!A2:A4"),MATCH(A2,INDIRECT.EXT("'C:\Documents and Settings\aosgood\Desktop\INDEX PRACTICE\Layer1\Layer2\"&B2&"\["&B2&".xlsx]Sheet1'!B2:B4"),0))

try that one.

Well it was actually #REF so I know there is something I am doing wrong. I also noticed the exact match was missing at the end and already added it in so I am still pretty sure its just me not thinking about the process of doing this right.

I have attached a sheet with the layout I think you were suggesting. The answer will obviously be #ref because it has no sheets to look at but hopefully you can see what I am doing and then tell me how wrong I am :D

Imagining for 1 second that B1001111 was the account number in another workbook called Barnet. I haven't been able to correctly tell the formula where to look like in an INDEX formula when you tell it to find the row and column and where it intersects.

It might be there where I am screwing up.