How to call out a worksheet by text in a cell

Posted by Brad on June 26, 2001 11:24 AM

I am trying to make a formula that will do a sumif on a certain range of cells. I want the range it sums to change with the text entered in cell B3. For example I have to sheets called "AA" and "BB". Instead of having "=sumif('AA'!B1:B5, 3, 'AA'!C1:C5)" I want it to key the worksheet off of whatever is in cell B3 of the sheet called "CC" (the sheet where the formula is written). So something like "=sumif('"=b3"'!B1:B5, 3, '"=b3"'!C1:C5)" so if I enter "AA" into B3 it will sum the data on sheet "AA". The formula I proposed doesn't work though. Any thoughts?

Brad

Posted by Russell on June 26, 2001 11:44 AM

Use the INDIRECT formula:

=SUMIF(INDIRECT("'" & B3 & "'!B1:B5"),3,INDIRECT("'" & B3 & "'!C1:C5"))

Hope this helps,

Russell

Posted by Brad on June 26, 2001 12:02 PM

Russell,
Is there a way I can do this so I can drag the formula down and have it change to =SUMIF(INDIRECT("'" & C3 & "'!B1:B5"),3,INDIRECT("'" & C3 & "'!C1:C5")) ?
When I drag down it stays as '" & B3 & "'

Brad

Posted by Russell on June 26, 2001 12:54 PM

First of all, if you drag it down, it should say B4, not C3. And it should work -- it did here.

Posted by Brad on June 26, 2001 1:35 PM

Thanks Russell, I had put the "$" in the wrong place, was wanting to hold the column static but I did the row instead. So when I drug down it was the same answer. Thanks
Brad