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]

Try replacing T1 with:
INDIRECT(T1)

 

[dlorenc]

details...details..details...

='https://Construction Weekly Reports/[P02-WS.xlsx]indirect(t1)'!D$4 - gives me a #REF! tells me it can't find worksheet indirect(t1) .. is its part of the

='https://Construction Weekly Reports/[P02-WS.xlsx]'indirect(t1)!D$4 - The formula you types contains an error... really informative..mm?

any hints on the structure of the command.. or how to trouble shoot it...?

 

[JoeMo]

Maybe like this:

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

 

[dlorenc]

soooo close... I think... #REF!

it throws up on the indirect ...

Evaluate Formula shows:
... [P02-WS.xlsx]"&INDIRECT("2.4.12")&'"!"&D$4

Doing an 'evaluate' on this I get
....[P02-WS.xlsx]"&#REF!&'"!"&D$4


whatchathink? (and thank you sooo very much for the help.... I think you are really close...)

 

[dlorenc]

my guess..?.. the double quotes are the problem..?.. "2.4.12" when evaluated...?.. as it is a rotten number I used to name the worksheets (I wanted a date format, and worksheets threw up on a slash for the date; so I used a dot)...

in T1... I just typed in the number 2.4.12 ... do I need format that somehow so the indirect tag resolves to 2.4.12 not "2.4.12"....???

 

[JoeMo]

my guess..?.. the double quotes are the problem..?.. "2.4.12" when evaluated...?.. as it is a rotten number I used to name the worksheets (I wanted a date format, and worksheets threw up on a slash for the date; so I used a dot)...

in T1... I just typed in the number 2.4.12 ... do I need format that somehow so the indirect tag resolves to 2.4.12 not "2.4.12"....???

Just entering 2.4.12 in T1 should be fine.
What happens if in the indirect function you replace T1 with "2.4.12" (including the quote marks)?

 

[dlorenc]

Just entering 2.4.12 in T1 should be fine.
What happens if in the indirect function you replace T1 with "2.4.12" (including the quote marks)?

#Ref! ---- it resolved to the exact text as above

indirect("2.4.12")

tells me the T1 reference to 2.4.12 -> "2.4.12"
is exactly the same as just typing it in as "2.4.12"...

 

[JoeMo]

#Ref! ---- it resolved to the exact text as above

indirect("2.4.12")

tells me the T1 reference to 2.4.12 -> "2.4.12"
is exactly the same as just typing it in as "2.4.12"...

I'm confused. Did it still return #REF? If you enter the reference to T1 in lower case (i.e. indirect(t1)) does it become INDIRECT(T1) after you press enter?

You can also try dropping the leading quote mark like this:

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

</pre>

 

[dlorenc]

I'm confused. Did it still return #REF? If you enter the reference to T1 in lower case (i.e. indirect(t1)) does it become INDIRECT(T1) after you press enter?

You can also try dropping the leading quote mark like this:

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

</pre>

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...