VLOOKUP - Refrencing a self-generated workbook name?

[Ebony Temptress]

Hello!

Okay, I need advice. I'm working on sort of template in excel used to pull data from several other workbooks.

Every workbook has its own name that follows a certain format. For example, "S2_BUILDING_ROOM.xlsb" and the part that changes is "BUILDING" and "ROOM." So long story short the template uses ="S2_"&N10&"_"&O10&".xlsb" to create a cell that says "S2_BUILDING_ROOM.xlsb". N10 and O10 are lookup formulas to grab the building name and room number. I want to make a formula that will look at this cell and pick up data from specific cell. It would look like "=[S2_BUILDING_ROOM.xlsb]Sheet1$C$33" only instead of entering "S2_building_room.xlsb" manually, I want to use the cell that generates this ID.

The reason for this is that I'm trying to automate a lot of work. On one page I put in the BUILDING#, and then each additional sheet will automatically generate its own ROOMID, then grab the appropriate data.

Does this make any sense?

Thanks!

[loginjmor]

Hi -

I'm not sure I completely follow what you are trying to do. Could you post a sample spreadsheet with any sensitive data removed, and an example or two of your desired outcome?

[Ebony Temptress]

Posting an example is a little more complicated because the workbook is linked to several other workbooks, but I'll see if I can be a little more specific.

Okay, so for this project I've sampled 4 rooms in 40 buildings. I'm building a workbook that will summarize the data for each building, and I'm trying to automate it. Every building has an ID number, BUILDING1, BUILDING2, etc. And in that building each room is ROOM1, ROOM2, etc.. The way I have it set up, the workbook has 5 tabs. A Building, Room1, Room2, Room3, Room4. I put in the BUILDING# on the building tab, and then the ROOM tabs generate their own ID by this formula: "=(Building!Q5&"_"&L4" (L4 says ROOM1 on page Room1, ROOM2 on page Room2, etc.) So the result is "BUILDING#_ROOM#". Lets call this ID cell "B2". Then there are other cells that will reference the cell B2 to VLOOKUP another piece of data... "=VLOOKUP(B2,....)" This works fine because B2 is just a reference point inside another workbook, the table_array (F:\folder\....[...]) is the same for all classrooms.

Now, I have 200 workbooks that are all separately named, but structured exactly the same. I need each one of these ROOM tabs to look at C48 and report that value. So what I've done is create the name of the file using the "&""&" formula. Lets say Cell A1 is looking up the building number, and cell B1 is looking up the Room number, so C1 is putting them together to make S2_ (this is just a random prefix that they all have) S2_BUILDING#_ROOM#.xlsb. Can I make a lookup that will reference C1 for the workbook name and pull C48 from it? So it will basically be =[C1]Sheet1$C$48 where C1= "S2_"&A1&"_"&B1&".xlsb" => (which in the cell reads S2_BUILDING#_ROOM#)

[loginjmor]

Hi -

This is a little tougher than it looks. The INDIRECT formula returns a cell reference from a text string. For example, =INDIRECT($C$1&"Sheet1$C$48") would return whatever is stored at that cell reference as long as that workbook is open. But if you are working with 200 workbooks, I doubt you want to open them every time you want to refresh the data.

So, there is a VBA workaround you can copy and paste into your worksheet located at:

https://www.experts-exchange.com/que...workbooks.html

Hope that helps.