Dynamic file name change within a Formula based on a Cell contents name.

This is a bit difficult to explain but let me try.

I have a spreadsheet that has formulas that gets data from an external spreadsheet (meaning not opened so hence cannot use INDIRECT), and based on the cells data needs to dynamically update in that formula to that name.

So in the example below the file in the C:\Users\jay_2\Desktop\Personal Calendars\ folder is NAME 1 Calendar.xlsx but rather than hard code that I would like to be able to have a cell with the NAME in it and whatever I chnage that to wil then update the formula to be that external filename.

EXAMPLE BELOW--------

IF(ISNONTEXT(VLOOKUP($A8, 'C:\Users\jay_2\Desktop\Personal Calendars\[Name 1 Calendar.xlsx]Sheet1'!$A$3:$B$500,2,FALSE))=TRUE,"",VLOOKUP($A8, 'C:\Users\jay_2\Desktop\Personal Calendars\[Name 1 Calendar.xlsx]Sheet1'!$A$3:$B$500,2,FALSE).

not sure if know what you mean. you want a dir in one cell and name of file in another? that easy. but you not doing that now. you using lookup function.

OK as I said it is difficult to explain what I am trying to do

Anyway that formula is used in a cell to refer to an external spreadsheet and a the data in that sheet to find some info and then place that found info back into the first sheet.
The filename will change based on the specific cell data in the original sheet to know which external filename to lookup the data from.

The lookup part of the function is to find the data within the file and then bring that back to the calling spreadsheet cell. So in essence the there is a cell in the calling sheet that has a name which i want to then populate into the formula
that will be the name of another spreadsheet with data in it that I will use Lookup to find and then bring back to that original cell in the first sheet. The problem is right now I need to manually type in the
formula for each of hundreds of cells the name of the needed spreadsheet rather than by just using a cell with the filename in it just populate into the formula automatically.

Does this help?

Jay

Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file.

Something like

=IF(ISNONTEXT(VLOOKUP($A8,INDIRECT("'"&A2&"[" & A3 & " Calendar.xlsx]Sheet1'!$A$3:$B$500"),2,FALSE))=TRUE,"",VLOOKUP($A8,INDIRECT("'"&A2&"[" & A3 & " Calendar.xlsx]Sheet1'!$A$3:$B$500"),2,FALSE))

A2 = C:\Users\jay_2\Desktop\Personal Calendars\

A3=NAME1

INDIRECT is a volatile function and can/will impact performance if too many files are open.

i see jay. well yes, sounds like you need absolute reference feature. you know how to do that? you already doing it though. issue here is i think you not explaining well. and yes, john says file will help. agreed here. always does help. sounds like reference type is needed or you need lookup table of files names and locations. many ways to do this i think. but again, you explanation not clear. =(

OK Loaded a smaller version of program. So once you open the scheduling and allow it update from folders you will see hopefully it populates various days depending on the column name specific days in that names file.
So my desire is that when I change a column name from Ranger 1 for example that the formula in the cells underneath that column heading gets changed to that name. I obviously will then need to make a file or change a file from the original name Ranger x to the new name so it will link correctly.

Does any of this make sense?

You an email directly and I can give you my phone if you wish to talk or maybe teamview to my desktop

Well actually I don't think you say that publicly on form I think that against rules. But yes some make sense some does not. But I think I know what you want. I'm not at my computer now. We'll take a look soon

Woops then I am sorry did not know that was a NO NO.... Anyway whatever you need for more info let me know.
Thanks

to tallk privately with someone here you go to this page and type their name in

https://www.excelforum.com/private.php?do=newpm

Thanks for the reply. I thought I mentioned that we cannot use INDEX since the remote file (you called LEAVE) cannot be part of the main system. I need it to be a separate workbook and hence unless open when opening the
Scheduling file INDEX does not work.

I do like or dont mind the single file versus one for each ranger but again would like to know how to make it a separate file to the scheduling system.

Thanks

BTW the extra space was an typo error so should not need the trim function once corrected.

I guess is there a way to make it an external file so it can be managed separately from the scheduling spreadsheet and reference as an external sheet whenever the scheduling system is opened?

Not sure what AK1`1 and AL11 are showing me since they are blank?

Also when I try to change a RANGER # to a name I get a 1004 worksheet error?

See attached

"Rangers Calendar" is consolidated file

This is not correct you have dates in the Calendar file that do not appear in the schedule??

So ? The Calendar is a whole year so it makes no difference that Schedule is a month; we are only comparing Schedule vs Calendar.

I am sorry but not sure where the rangers folder is not since the schedule shows it calling something in APPDATA

In the "Schedule" wokrbook you will to change the VLOOKUPs to reference your directory.

=VLOOKUP($A8,'C:\Users\Owner\Downloads\[Rangers Calendar.xlsx]Sheet1'!$A$7:$T$371,COLUMNS($A:B),0)

Know idea about the call: are you on Excel 2003? If not, update your profile please.

In cell D8 the formula is looking for =VLOOKUP($A8,'C:\Users\jay_2\AppData\Local\Temp\[Rangers Calendar.xlsx]Sheet1'!$A$7:$T$371,COLUMNS($A:B),0) not the location you show and hence nor sure where the data is.

I am using Excel Professional Plus 13

So what you are saying is make this one fixed external file that has a fixed call in the lookup function but everyone is in the single file rather than a separate one that can be dynamically called for each name. I guess I can make that work but we really wanted it so that each ranger could have his file add and delete dates for varies specific reason (ie. VA, Pd etc.) and once loaded back to the saved folders it would automatically be used by the scheduling program. You solution can work but then they will have to submit updates to the program manager to update the single file.

Where did you unload the file to?

C:\Users\jay_2\AppData\Local\Temp\ seems like a default on your computer: search for the file.

This is where I would expect you to store it from your earlier post):

C:\Users\jay_2\Desktop\Personal Calendars\

This forum is amazing with the help and understanding you offer. I never figured that I could make it clear as to what I was trying to do with my convoluted formula approach that I tried to develop and yet
you folks came up with some answers that will work. While not exactly what I was looking for it can work and I appreciate your all chipping in. If someone comes up with a way to do the same thing but with separate ranger files that can be dynamically called based on the column name adn need not be actually part of the Spreadsheet sheets I would love to hear about it, until then I will try to work with this solution.

The issues with using INDIRECT are

(1) The files have be OPEN so potentially you have 19 "Ranger" files open
(2) INDRIRECT is volatile so can impact performance

I would have thought maintaining a single file is better than managing 19 !

Good luck!