Using worksheet names in formulas

[jschrock]

Hi All,

Is it possible to reference a workbook name like a formula?

For example...Say I have a tab named 'MAY 12' and I am referencing that tab in a YTD tab that I have previously made and I have a column that is pulling all those numbers, but in the sumifs formula I have instead of referencing


=SUMIFS('MAY 12'!$E$1:$E$200,'MAY 12'!$A$1:$A$200,$B6)

I would like to reference

=SUMIFS('B2&" "12'!$E$1:$E$200,'B2&" "12'!$A$1:$A$200,$B6)

Or something of that nature so when I copy this formula to the next column it automatically looks at 'JUN 12' instead of 'MAY 12'

Hopefully that explains well enough to know what I mean. Thanks!

 

[Andrew Poulsom]

Try:

=SUMIFS(INDIRECT("'"&B2&"'!$E$1:$E$200"),INDIRECT("'"&B2&"'!$A$1:$A$200"),$B6)

 

[jschrock]

In addition the reason I am worried about it is because the tab may not be created until a later date and I want to be able to copy over when it is created.

 

[jschrock]

It doesn't seem to be working. Will that formula work for the tab names at the bottom?

 

[jschrock]

OK...it was a problem on my end. It works....It's great....another win for Mr.Excel!

Thanks for the help!