Lookup Data from Dynamic Online Document

[jfeldman1994]

Hi all,

I have tried numerous ways to approach this problem to no avail but maybe someone can finally help me solve it. I have to pull dates from excel spreadsheets that are stored online. There are around 50 different spreadsheets that I need to get the same data from. Each spreadsheet has the nearly same title, "P401_9600021345.xlsm" and the exact same format in excel. I already have a VLookup and an index match formula that go to that specific document and get the data. i.e.

"=VLOOKUP(D1, 'http://google.com/org/osmp/metrics/Shared Documents/Supply_Chain/Excel Systems P401 Data/401/[P401_9600021345.xlsm]Template!$A$4:$E$37,2, FALSE)"

"=INDEX('http://google.com/org/osmp/metrics/Shared Documents/Supply_Chain/Excel Systems F401 Data/401/[P401_9600021345.xlsm]Template'!$A:$K,(MATCH(D$1,'http://google.com/org/osmp/metrics/Shared Documents/Supply_Chain/Excel Systems F401 Data/401/[P401_9600021345.xlsm]Template'!$A:$A,0)),2)"

Both of these work fine. However I need the links to be dynamic. I have a column with all of the different P401 Numbers. I wanted to have the URL build depending on what value is in the column.

i.e.
A1: 9600021345
A2: 9600025648
A3: 9600045679
so on...

"=VLookup(D1," ' "&"http://google.com/org/osmp/metrics/Shared Documents/Supply_Chain/Excel Systems P401 Data/401/[P401_"&$A1&".xlsm]Template!$A$4:$E$37",2, FALSE)

Any thoughts would be greatly appreciated. Note: All of this information is not the real data due to proprietary complications so these are just made up examples of what I am trying to accomplish. They are based on the real format though.

 

[sijpie]

You should be able to do this through the INDIRECT() function.

So you have the number part of the file name in column A
Then in Column B you could create the file name with
B1="'http://google.com/org/osmp/metrics/Shared Documents/Supply_Chain/Excel Systems P401 Data/401/[P401_" & A1 & ".xlsm]Template'!"
in C1 you could have =B1 & "$A$4:$E$37"

Then your vlookup would become:
=VLOOKUP(D1,INDIRECT(C1),2,FALSE)

in the same way you can build the Index formula

 

[jfeldman1994]

The only problem is that indirect needs to have the file open on the computer in order to work. These files are all online and so none will ever be able to be opened directly on the computer, there are at least 50 files.

 

[sijpie]

In that case create a macro that you run first to create the required formulas from the list of P401 numbers.