Indirect Formula help

[Matt71]

Hi I'm trying to use the indirect function to populate the following formula

=SUM('worksheet_1:Worksheet_100'!U2) - This formula works exactly as I want it to.....

The values worksheet_1 and Worksheet_100 are to be volatile and taken from cells AA1 and AA2. Cell AJ3 is a number (2 in this case).

I've tried the following syntax and keep getting a #REF error and it's driving me insane!!!

=SUM(INDIRECT("'"&AA1&":"&AA2&"'!U"&AJ3))

I've also tried

=INDIRECT(CONCATENATE("=SUM('",AA1,":",AA2,"'!U",AJ3,")"))

And tried putting =CONCATENATE("=SUM('",E1,":",N1,"'!U",AJ3,")") in cell AA3 then using =INDIRECT(AA3)

Please can anybody help?? A different way of doing the same thing perhaps?

Nothing works..... PLEASE PLEASE PLEASE SAVE ME FROM THE HELL OF EXCEL !!!!

 

[pgc01]

Hi Matt

Indirect does not support 3D references, that's why your formulas do not work.

You'll have to write a list with the names of the sheets that you want to include in the Sum() and use it to generate an array of references.

There are many examples in this board. If you have problems post back.

 

[Matt71]

Thanks for saving me hours more frustration with the indirect function............. much appreciated.

I have a list of the sheets to include located in cells A3-A102 on a worksheet called "Summary" - Not too sure about how to use the array thing is there a way to pass that list into the Sum()? The only issue is that the list won't always be full....

Thanks Again.

Matt.

 

[pgc01]

Hi

Try:

=SUM(IFERROR(N(INDIRECT("'"&A3:A104&"'!U"&AJ3)),0))

This is an array formula, you have to confirm it with CTRL-SHIFT-ENTER and not just ENTER.

 

[Matt71]

Thanks for the help... I put the laptop to bed about 16.45....for it's own safety..........I'll give it a whirl in the morning and let you know how it goes.

 

[jabrown61]

Hello,

I too have had this issue. It is the syntax Here is an example formula that uses the Month-end date 201401 as a tab to pull data from:

The value in B1 = 1/31/2014

[TABLE="width: 68"]
<colgroup><col></colgroup><tbody>[TR]
[TD] =SUM(IF(FREQUENCY(INDIRECT("'"&TEXT(B$1,"YYYYMM")&"'!$C:$C"),INDIRECT("'"&TEXT(B$1,"YYYYMM")&"'!$C:$C"))>0,1))
[/TD]
[/TR]
</tbody>[/TABLE]

This counts unique values in the column.


[TABLE="width: 68"]
<tbody>[TR]
[TD] =SUMIFS(INDIRECT("'"&TEXT(B$1,"YYYYMM")&"'!$K:$K"),INDIRECT("'"&TEXT(B$1,"YYYYMM")&"'!$M:$M"),">="&DATE(YEAR(B$1),MONTH(B$1),1),INDIRECT("'"&TEXT(B$1,"YYYYMM")&"'!$M:$M"),"<="&SUMMARY!B$2)
[/TD]
[/TR]
</tbody>[/TABLE]

This formula sums data in a column between 2 dates (B2 = 1/28/2014)

Hope this helps.

 

[Matt71]

@pgc01 - Wow.... That worked perfectly. Thank you doesn't quite cover it but THANK YOU!

I just need to brush up on my understanding of array formulas - They look like a powerful ally.......

One cautionary note for anyone else trying the same thing - I copied the List of names through from another page using the formula =Worksheet_name!A1 and copied it into subsequent cells.

Where the cell is blank the formula returns a 0 value, this seemed to cause the array formula some issues.

To return a blank value I used =IF(ISBLANK(Worksheet_name!A1),"",Worksheet_name!A1)

I hope this helps some folks out in the future.

 

[Matt71]

Thanks for the help J,

The array formula sorted things for me, when I get a few moments spare I'll try your method too. It never hurts to have an extra get out of jail card.....

Thanks

Matt.

 

[pgc01]

I'm glad it helped. Thanks for the feedback.

One cautionary note for anyone else trying the same thing - I copied the List of names through from another page using the formula =Worksheet_name!A1 and copied it into subsequent cells.

Where the cell is blank the formula returns a 0 value, this seemed to cause the array formula some issues.

Couldn't reproduce the problem. The IFError() in the formula should take care of any error in the worksheet names list.