External references, variable or dynamic formula

[dlorenc]

This works:

='https://Construction Weekly Reports/[P02-WS.xlsx]2.4.12'!D$4

I would like to replace 2.4.12 with a cell reference (T1, which would contain "2.4.12" ...)

I know this does not work:

='https://Construction Weekly Reports/[P02-WS.xlsx]'T1!D$4

thank you for the help!


if it matters, the external excel sheet is in a sharepoint site...

 

[JoeMo]

1. sorry.. changing the T1 to "2.4.12" (with double quotes) produces the same #REF! error....

when I change the T1 to t1, it does change the formual to T1... and produces the same #ref! error.

2. removing the double quote gives ma a "The formula you typed contains an error"... message...

Does this work?

="'https://Construction Weekly Reports/[P02-WS.xlsx]"&T1&"'!"&D$4

 

[dlorenc]

Does this work?

="'https://Construction Weekly Reports/[P02-WS.xlsx]"&T1&"'!"&D$4

mm... this one does not ... totally evaluate...calculate?... it seems to think the lead url is text but resolves the T1 and D4 to local cells...??.

displays as

'https://Construction Weekly Reports/[P02-WS.xlsx]]2.4.12'!305

T1 = 2.4.12

D4 in this worksheet is 305 ...

 

[Andrew Poulsom]

The entire formula must be passed to INDIRECT:

=INDIRECT("'https://Construction Weekly Reports/[P02-WS.xlsx]"&T1&"'!D$4")

but that said, INDIRECT doesn't work with a closed workbook.

 

[dlorenc]

I opened the external workbook in the same excel session.

I still get a #REF!.. when I do the evaluate steps I find:

indirect("'https://Construction Weekly Reports/[P02-WS.xlsx]2.4.12'!D$4") ... looks pretty close..???..

 

[dlorenc]

hold the phone!!!...

I closed my workbook..(leaving the external workbook opened... leaving the external workbook active, I did an recent...open the workbook doing the referencing..and it WORKED!... bit strange on why closing and reopening seemed to make a difference..but it did...??..

ok..so..now I have a working reference.. I can do a process change that requires the user to open all twelve workbooks from which I want do a data pull.. (ick)... but it is..what it is....

unless someone can come up with a different process... this might be as good as it gets...mm?...

in which case..THANK YOU JOEMO and ANDREW..y'all ROCk!