Indirectly referencing a worksheet within a formula

I've been struggling with these for a while, here are the details.

I have a formula in worksheet "March" that uses data from worksheet "Family" (all in the same workbook). In order to reference the data, I use the following formula:

=SUMIF(Family!B$2:BA$2,MONTH(A$1),Family!B$22:BA$22)

The row that this data is in is named "Family", to correspond with the worksheet it is talking about. However, in the formula (as you can see), I had to type out "Family!" to get the reference I wanted.

What I want to be able to do is to get the formula to automatically detect what the row title is, understand that it is referencing a different worksheet, and automatically update the formula with that worksheet.

The XXX marks the part of the formula I want to do the work:

=SUMIF(XXXB$2:BA$2,MONTH(A$1),Family!B$22:BA$22)


The reason I want this is so that if I have a new workbook, "Friends", I want to be able to type "Friends" as the row heading and have any data I drag down automatically reference "Friends" (instead of "Family").

I have tried using the INDIRECT function with little success. Any help would be appreciated!

So, is "Family" (or "Friends") in A2? If so you could change your formula to this:

=SUMIF(INDIRECT("'"&$A2&"'!B2:BA2"),MONTH(A$1),INDIRECT("'"&$A2&"'!B22:BA22"))

Hope this helps.

Pete

This was perfect, thank you so much!