how to use INDIRECT in a formula

[mpatsis]

I am new to INDIRECT and have read how to reference a single cell using INDIRECT but I'm hoping someone can help with a formula I have. I have a sheet for each month Jan 15, Feb 15 etc. and the summary on a separate sheet. Can INDIRECT be used within the below formula? I've tried to replace the Apr 15 but as soon as I add a range I get and error.


=SUMIFS('Apr 15'!$AD$60:$AD$190,'Apr 15'!$D$60:$D$190,$C29,'Apr 15'!$E$60:$E$190,$D29)*BJ29

Any help is appreciated.

[FDibbins]

So your range stays the same, but you want to reference the sheet name from a seperate cell?

Assuming your month/file name is in A2...
=SUMIFS(indirect("'"&A2&"'!$AD$60:$AD$190"),indirect("'"&A2&"'!$D$60:$D$190").........................

[Tony Valko]

Describe how or why you want to use INDIRECT.

[Pete_UK]

Do you have the text Apr 15 (or a date formatted to show as that) in a cell somewhere? Is the idea to change the value in that cell and thus get data from another sheet?

Pete

[mpatsis]

Great, thanks!

[mpatsis]

I am trying sum data from various sheets ie. Apr 15. I have been using find and replace but think it would be more efficient to just have to change the name of the sheet once and have it update all columns.

[FDibbins]

upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

[Tony Valko]

I am trying sum data from various sheets ie. Apr 15. I have been using find and replace but think it would be more efficient to just have to change the name of the sheet once and have it update all columns.

OK, try something like this...

Enter the sheet name as a TEXT string in some cell.

A1 = Apr 15

Then, your formula would be:

=SUMIFS(INDIRECT("'"&A1&"'!AD60:AD190"),INDIRECT("'"&A1&"'!D60:D190"),$C29,INDIRECT("'"&A1&"'!E60:E190"),$D29)*BJ29