I currently have a cell that references to a workbook by:
='P:\Tools\Transient Pickup Tool\ATLDO\ATLDO Pickup Tool 1.0.5.xlsm'!DaysLoaded
I would like to make it so that ATLDO is just referring to cell A2 so something like:
='P:\Tools\Transient Pickup Tool\" & A2 & "\XXXXX Pickup Tool 1.0.5.xlsm'!DaysLoaded
I would like to be able to drag it down a list without having to change each one individually. Where XXXXX is I would be referring to a cell. So 2 locations I'm trying to change.
You would use INDIRECT to do that but I don't know if it's happy to work with closed workbooks, for example.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
I will need the file to be closed, I'm trying to get 108 files at once and don't want them all to be open. If I did want to use INDIRECT what would that function look like?
According to the latest help file https://support.office.com/en-us/art...1-92b6306fa261 INDIRECT() only works if the file is openWhat exactly are you doing here (other than pulling data from 100 different files)? What does each file look like? We might have better suggestions if we understand the context for you question.Originally Posted by Excel help
Are you willing to entertain the possibility of combining all 108 files into one file? I don't work with databases, but it often seems that, in the hands of someone who knows databases, the suggestion is to compile all the data into one big database (Get and Transform can usually do this fairly easily), then query the database (a lookup function or similar).
One possibility is to use VBA to dynamically write the formulas for you to avoid INDIRECT, but I would have to have a sample file to write it specifically for your case.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks