Dymamic update for references in cells pointing to other sheets or sheets in workbooks

[Whack]

I'm a neophyte with EXCEL and I'm trying to update a file reference in a formula .

My formula looks like this:

=IF(A101="","",'D:\@Advocate\new\[@Client Model.xlsx]Client Info'!$C$4)

Id like the formula to dynamically update to look like:

=IF(A101="","",'D:\@Advocate\new\[Rubble Barney .xlsx]Client Info'!$C$4) <---Hard coded this works...

but there are several entries in each row that needs updating (35) and it's cumbersome, time consuming a error prone to manually update each of the 35 cells... yes, I know I can use the replacement on a "row" and replace all in one blow, but that's still a problem.

Cell AF101 contains Rubble Barney .xlsx

I figured a formula like:

=IF(A101="","",'D:\@Advocate\new\['ConCat(AF101)']Client Info'!$C$4)

but I received the following error message.

I've also tried :

=IF(A101="","",'D:\@Advocate\new\['ConCationate(AF101)']Client Info'!$C$4)

And I get the same error message

Can some kind soul please give me a clue how to get this kind of update to work... I have literally thousands of entries that need to be dynamically updated.

Many thanks in advance
---Whack

 

[awoohaw]

Have you considered using power query?

 

[Whack]

Thnak you... but I've no idea what "power query" might be..

I tried to follow the instructions to install the XL2BB add-in. Once I got it installed, I couldn't figure out how to use is and I kept getting a warning I couldn't use the function... perhaps it's a macro? I dunno.

Since my previous post, I've tried using

the following functions:

CONCATENATE
ADDRESS
INDIRECT


But I'm still not making much headway

Thanks in advance
---Whack

 

[awoohaw]

If you are interested in learning Excel better I suggest looking at some youtube channels including the Mr. Excel Channel.
Here are some other excel channels that have great content:
MyOnlineTrainingHub, Leila Gharani, and ExcelIsFun.

Here is an Excel Is Fun video that may help you figure out how to consistently and easily get new workbooks into your running mass workbook:
It may take a while to watch but Power Query will take just a few clicks each month once you have it set up.

 

[awoohaw]

Additionally, using power query will not require being logged into network where your data resides (unless you need to refresh the data source(s)).

 

[Whack]

Thanks..

I'll go look

 

[Whack]

awoohaw,​

Thank you so much for the powerquery reference and video link... I'm afraid at this time, that's not quite what I need... is there a way to do what I inquired about initially?

Thanks in advance.
---Whack

 

[Anonymous1378]

The way to change a cell reference via nothing but functions is to use the INDIRECT() function. However, it does not work for external sheets, as is your use case here, unless you would like to also open every single sheet you are referencing. Your closest option is probably to use VBA to update the formulae for you.

 

[awoohaw]

awoohaw,​

Thank you so much for the powerquery reference and video link... I'm afraid at this time, that's not quite what I need... is there a way to do what I inquired about initially?

Thanks in advance.
---Whack

I cannot recall any other way. I'm not really sure why you say "it's not quite what I need" as one of power query's main functions is to aggregate data from disparate sources. If you do want to go into power query, there is a forum here in Mr. Excel called Power Tools that you can get some PQ specific assistance with.
Here is the link to that forum: Power Tools

Best wishes.