Get Tab Sheet Name into a formula

[Physical]

I have been trying to change the data on a sheet by having the formula offset and using a dropdown list with the contents being Tab/sheet names. Unsuccessfully.
If you have a formula like =SUMIF(SDALL!$C$5:$C$104,"D",SDALL!D$5:D$104)
and wanted the SDALL! to come from a Drop Down List, How can this be done.
I have 9 Tabs/Sheets, all the data on each sheet has the same layout structure just different months values.
Regards - New Chap

 

[GlennUK]

Like this:

=SUMIF(INDIRECT(my_drop_down_choice&"!$C$5:$C$104"),"D",INDIRECT(my_drop_down_choice&"!$D$5:$D$104"))

 

[Physical]

Two things: 1 worked perfect thank you. 2 how can I use it with the formula below, probably should have post this one first as it's the more dominant.

=OFFSET(CTOOL!$E$1,Calc!$D$5,0,1,1)

Can't believe I got such a quick response, been trying to solve for a week.

 

[GlennUK]

You mean something like this:

=OFFSET(INDIRECT(my_drop_down_choice&"!$E$1"),Calc!$D$5,0,1,1)

 

[Physical]

I tried this, now while the first one pick up the name range of the list box :-
=SUMIF(INDIRECT(SDL&"!$C$5:$C$104"),"D",INDIRECT(SDL&"!$D$5:$D$104"))

This one only see's the cell ref "V3"
=OFFSET(INDIRECT(V3&"!$E$1"),Calc!$D$5,0,1,1)

But if there's a way around it it would be perfect.
Thanks Glen

 

[Physical]

Glen, Got it to work, it was my incorrect interpretation.
Absolutely fantastic, can can know use the dashboard to view all months in one screen, scroll through 250 different screens and now (big Now) using the drop down alter which group to display, it all updates quick. And to top it only 5.66MB
previously in excess of 25mb. Thank you very much.

 

[GlennUK]

Am so glad it's all working smoothly. It's a great feeling when that happens. It's been a pleasure to help.