VLOOKUP to other workbooks/tabs from a cell list

[chronotonFlux]

I am trying to link several workbooks together so that I may provide my work with an easy way to calibrate tools.

I have the main interface excel workbook which takes user entry and I want it to populate data based on different dropdown selections and I them want to save data to different files (as specified by the dropdown)

We have several scales which each have different properties such as serial number, model etc. I've made a Scales.xlsx file that has a tab for each scale and the tabs are named by scale IDs (ex. SCA-0101, SCA-0102..). I made sure the tabs all share a common template. They all have the same cell locations that I'm going to be pulling data from.

I want excel to generate the data stored in Scales.xlsx[SCA-0101] if the user selects that from the list and so on.

I've tried to specify each scale ID into a list(used for the dropdrown), along with the file/tab path, but VLOOKUP won't work if I attempt to use a cell value instead of the usual range argument.

Could use help.

 

[mrshl9898]

So you want to change the book you are looking in by changing a cell to the book name?

I was able to replicate:
=VLOOKUP(A1,'[Scales.xlsx]Sheet1'!$A:$A,1,0)

By using:
=VLOOKUP(A1,INDIRECT(B1),1,0)

With in B1:
="'"&"[Scales.xlsx]Sheet1'!$A:$A"

 

[chronotonFlux]

That may work, but that's not exactly what I'm looking for

I have 2 workbooks, Database.xlsx and Computers.xlsx

Database.xlsx has a sheet Lists which has a list of all the devices that are stored in Computers.xlsx
The list has 2 columns (ID, and FileLocation/Tabname)
Computers.xlsx has a sheet for each volume device and they are named according to their ID number AAX01-0001, AAX01-0025 etc..
Each of the Computer sheets looks the same, and stores all the basics of each (manufacturer, model, serial etc...)

I have a drop down list in the Database.xlsx file which has all the computers by ID and what I want is to populate that sheet with the information about each of the computers based on the ID.

I have a cell that's going to be hidden that will act as the VLOOKUP to get the filepath/tab location, but now I want that cell to betaken literally and concatinated with a specific cell location in the Computers.xlsx file.

For example, if the device AXX01-0010 is selected from the dropdown(E6), the nearby cell, H7 displays the path to the file/sheet

E6= AXX01-0010
H7 = VLOOKUP(E6, Lists!B3:C30,2,FALSE)

H7 returns: '[Computers.xlsx]AXX01-0010'!

I want cell J8 to return the manufacturer (which is located at cell D12 in each of the sheets)

I want to be able to do a relatively simple formula such as references the full location and concactinates that with the cell location, such as

J8= H7&D12
but that just returns the full path and cell, without actually going there and grabbing the information.
Instead of getting the manufacturer "Toshiba", I get "'[Computers.xlsx]AXX01-0010'!"

 

[mrshl9898]

Ok,

Trying to test this:

In A2: '[Computers.xlsx]AXX01-0010'!
In B2: =INDIRECT("'"&A2&"$D$12")

Returns Toshiba from D12 in the Computers file, in sheet AXX01-0010


So, I think you need:

=INDIRECT("'"&VLOOKUP(E6, Lists!B3:C30,2,FALSE)&"$D$12")

 

[mrshl9898]

Or =INDIRECT(VLOOKUP(E6, Lists!B3:C30,2,FALSE)&"$D$12")

Not too sure how the leading ' acts when the VLOOKUP adds the name

 

[chronotonFlux]

With INDIRECT all files referenced have to be opened.
I don't want to open the files

I'm basically just trying to do the same thing as ='[Computers.xlsx]AXX01-0010'!D5 does, but break it up in two parts

I've tried putting A1 = "'[Computers.xlsx]AXX01-0010'!" and calling it back to B1 = A1 & "D5" but all I ever get is EXACTLY that, word for word "'[Computers.xlsx]AXX01-0010'!D5"

I want it to treat A1 as the actual location which it treats it as if I type it into the formula. How do I get it to do that?

 

[mrshl9898]

Well if the file isn't open you need the file location also.

Just open the book, point it at that cell and close it to get the path

 

[chronotonFlux]

I do and it works when it has the full everything hardcoded in there, but if I want it to use information gotten from a cell for part of the address, it just starts printing the full path.

What I want it to do.
="'X:\DATA\Database\[Computers.xlsm]"&H7&"'!$D$12"

Where
H7 is a VLOOKUP of a list of different computers (currently set to: AXX01-0010
$D$12 is the cell location in all the computer sheets that has information required

What should get printed: Toshiba
What gets printed: 'X:\DATA\Database\[Computers.xlsm]"AXX01-0010'!$D$12