how do i change the formula to pull from the relevant tab?

[bigdan]

Hi folks, question please.
So Col A has the names of tab names in an external file.
Cell B1 shows the formula I need. I want to drag that formula down but I want to change the tab name in each instance.
How would I do this?

ABC	=[F23_Q2FCST_Procurement.xlsx]ABC!$CD$80
DEF
GHI
JKL

 

[PeteWright]

B1=INDIRECT("[F23_Q2FCST_Procurement.xlsx]"&A1&"!$CD$80")

 

[bigdan]

holy **** crap that worked!! thanks so much buddy

 

[bigdan]

i've tried this again but this time it's not working. i wonder if you can advise? I'm getting a REF error.

Tab	=SUM(test!A1:A10) (sample formula)
ABC	=INDIRECT("SUM("&A2&"!A1:A10)")
DEF

 

[Fluff]

Try it like

=SUM(INDIRECT("'"&A2&"'!A1:A10"))

 

[bigdan]

Try it like

=SUM(INDIRECT("'"&A2&"'!A1:A10"))

yes that works, thanks! can you tell me why that worked and mine didnt?

and unless i'm mistaken that code once simplified looks like this (keep in mind the value at cell A2 is "ABC") -> =SUM('ABC'!A1:A10)

most notably there's a single quote before and after the tab name. but i believe the correct syntax to reference that tab when doing so directly is =SUM(ABC!A1:A10) ie no quotes. so why the change?

 

[Fluff]

Some sheet names need to be enclosed in apostrophes, so I tend to put them in regardless, it just makes it more robust.

 

[bigdan]

Some sheet names need to be enclosed in apostrophes, so I tend to put them in regardless, it just makes it more robust.

ok thanks. but why did your formula work and not mine? simply b/c of the quotes?

 

[Fluff]

You hade the whole construct wrong, which is why it didn't work. The Indirect function needs to be wrapped in Sum, not the other way round.

 

[bigdan]

cool thanks!