Vlookup from files where the file & sheet names needs to be looked up also.

[mike2008]

This hopefully has a simple solution...

I have approximately 20 files with between 1 & 3 sheets, each of which needs to be consolidated using a vlookup or similar without merging them into one file due to the size.

Each sheet is named as the department to which the data relates, and each file is named according to the manager responsible for the combination of departments.

My consolidation needs to "dump" the data from columns Z to AN but on different rows within each file, and different combinations for each sheet.

I have an index within column Z which is a simple equation of MAX($Z$2:Z101)+1 for row 100 etc... the max number of lines to be transferred is then populated into Z1 to determine the number of lines to be consolidated for that sheet.

What I thought of was to firstly look up all the Z1's according to the sheet name (I don't care about the file name for the consolidation), and then have a row for each sheet name / row number combination valid, but then comes the difficult bit. How can I set the dependency in a vlookup to reference the correct sheet & file path within the vlookup? I don't know a way of creating a dependency in the vlookup for file name like you might for which column to reference.

I hope that all makes sence... & thanks for any answers you come up with.

Mike

 

[mike2008]

Sorry to bump the question, but does anyone have any ideas?

Many thanks

Mike

 

[Eric Golf]

Hi,

Check out the INDIRECT() function, think it might be what you need?

i.e. If you have a Path & filename in B1 and Sheet in C1 then you can construct vlookup as follows:-

=vlookup(A1,indirect(B1&C1&"A:Z"),7,false)

You may need to add in the single quotes & exclamation marks required for paths or sheet names with spaces.

Hope this helps,

Eric

 

[Deano]

Hello Mike

I would suggest you try one of two possible options. First one being Pivot Tables which can be used to consolidate more than one range. Secondly, you could add the source sheet name into your vlookup by using this formula example.

=REPLACE(CELL("filename",A8),1,FIND("]",CELL("filename",A8)),)

This formula brings back the sheetname by referring to a cell (in this example A8) within the sheet.

Not sure if it will help, but it's all I can think of.

Regards

 

[mike2008]

Thanks Eric / Deano, unfortunately none of those solutions will work, although the "Indirect" would work in principle if it were not for the following:

"Indirect" problem - This function requires the workbook to be open. Due to the number and size of the files (the largest being 45MB [due to a formatting problem that is another issue] it is not possible to have the files open at the same time).

"Pivot tables" problem - given the large number of sheets and files this would require a very complicated range of sheets, and would add little value over copying & pasteing each line. I need a dynamic solution that just needs a list of sheet names & the files they are contained in to work independently.

"Replace" funcition problem - As far as I can try, this only says the name of the current file, when in fact I am trying to tell Vlookup the name of another sheet and file path to look up data.

Does anyone know of a way to adapt "Indirect" or a similar verision - or is it possible to "trick" excel into accepting it the first time with the file being open and then ignoring it after (I know that Names are not supposed to be in other sheets etc, but it is possible.).

Thanks

Mike

 

[mike2008]

Hi - Does anyone else have a solution to this problem please?

 

[mike2008]

I have found a solution that worked, but would still be very interested if anyone can better it, if not, it may be useful for other people...

Using the "&" function I have created a text expression for the equations I needed to calculate, copy & pasted values into the next column, and then replaced the "vlookup" with "=vlookup".

 

[angiemeh]

I just posted a similar issue. I'm up for any workaround at this point.

You mention Using the "&" function & have creating a text expression for the equations you needed to calculate, & then copy & pasted values into the next column, and then replaced the "vlookup" with "=vlookup"

I did this, but with the =vlookup, it is reading it as a text and not a formula. I made sure the cell wasn't on text formatting and it wasn't.

Can you give an example using the &?

 

[mike2008]

Hi Angiemeh... this was my exact process replicated...

"G3" contains the text "$F3" with G4 having F4 etc...

"C3" contained the text for the file path "L:\2009\Profit Plan\Overhead Reviews\[General Office & Building.xls]"

"B3" contained the text for the sheet name "P637810103"

In "J3" the equation for linking the expressions is "="VLOOKUP("&G3&",'["&C3&"]"&B3&"'!$Z$2:$AN$500,5,FALSE)""

This equation is then copied down the required number of lines to point at the relevent paths and sheet combinations.

You then copy and paste special - values the list of equations in the following column, leaving the text expression of:

VLOOKUP($F3,'L:\2009\Profit Plan\Overhead Reviews\[General Office & Building.xls]P637810103'!$Z$2:$AN$15000,5,FALSE)

Find replace "vlookup" with "=vlookup" converts this new text expression into a valid lookup which can be manipulated by replacing the column number or equivilent as required:

=VLOOKUP($F3,'L:\2009\Profit Plan\Overhead Reviews\[General Office & Building.xls]P637810103'!$Z$2:$AN$15000,5,FALSE)

 

[angiemeh]

Cool, that worked! I created my text a little different, but the find/replace with = vlookup works.

I'm thinking I can just create a couple macros that copies the formulas down, makes them a text and then does the find and replace, so it can definately be automated.

Thanks for your help!