Using VLookup with a Dynamic Filename within a data validation

[adhbrown]

Hey Friends,

I've been looking and looking but cannot find answers to my question. I have a macro that gives a list of filenames within a folder. I then used data validation to put the file names in a list. What I now want is to be able to write a vlookup that spits out data based on what file name I choose. For example if I have VLOOKUP($A4,"Filename"PVBP Overall'!$B$14:$AO$43,B$1,FALSE) the vlookup formula should change each time based on the filename and populate. The problem I'm having is that excel doesn't like the fact that the filename is in a list. Please help!

Thanks!

 

[jono19872006]

So I think you would need to use the indirect formula to get the vlookup to recognise the range,
however the indirect formula would only work if the external workbook is open

So essentially you need to wrap a concatenation of the created range inside indirect()

For this you need
1. the workbook name eg Excel Doc,
2. the file type eg .xlsm,
3. the sheet name where the range is looking eg main
4. the actual range eg b3:b4
Eg you would need to create a formula to represent the text string below
'[Excel Doc.xlsm]Main'!$B$3:$B$4
If the text above was in cell B2 you would wrap it in the indirect function like below, nb the function didn’t recognise the first ‘ (apostrophe) as I had typed this text in manually in B2 so I needed to add it in the indirect function below to get it working, if you used a formula to bring it all together then you wouldn’t need this additional bit.
VLOOKUP(A1,INDIRECT("'"&B1),1,FALSE)

Also if you were using the vlookup to return a number field, you could use a SUMPRODUCT function to return the number value (on a closed or open workbook) – but this could only be set up with a few criteria and to return a number

 

[adhbrown]

Thanks for the response. Is there no way to access the data without opening the master file? The master file changes daily (same columns and headings just different data) and I want to be able pull certain columns from the data. Would a macro work better for this?

 

[jono19872006]

If you are using a vlookup to return a number then you can use the sumproduct function else if you are returning a text field I am not aware of a way to dynamically access data in a closed file unless someone else on here knows.