Incorporate the INDIRECT function correctly into a formula

[Harribone]

Hi,

It's been a while since I've been on here but it's good to be back! And I thought I would start by asking for help with the below, to make things simple I have made my formulas generic just incase anyone else would find this solution useful.

I have a formula to sum the values across several sheets, =SUM('1:100'!A1), and it does the job as expected adding all values in A1 across the sheets in that range.
I would like to replace the 1 and 100 so it uses values from two cells, lets say these are located on a sheet called DATA and the first cell is A1 and the second is A2.

I've tried my best to get this to work with the INDIRECT function but it just doesn't want to work for me! I've attepted this using the 2 ways below:

=SUM(INDIRECT("'"&DATA!A1&":"&DATA!A2&"'!A1")) creating a range using INDIRECT within the SUM function
=INDIRECT("SUM('"&DATA!A1&":"&DATA!A2&"'!A1)") using everything within the INDIRECT function

I know I've propably missed something simple but I can't for the life of me figure it out.

As always thanks for your help,
Harribone


EDIT
I have also tried creating a text string in a cell to generate the range to sum and then using the INDIRECT function referencing that cell. That hasn't worked so I think this might not be sommething that can be done...

[martindwilson]

if sheet names are realy just numbers wher k1 and l1 hold the numbers i couldn't get it to work with sum but sumif works
=SUMPRODUCT(SUMIF(INDIRECT(ROW(INDIRECT($K$1&":"&$L$1))&"!a1"),"<>"))

[Harribone]

Thanks for your work on this, i've never used sumproduct before so hadn't considered this approach at all.
After testing it does do the job but only when the sheets follow on from each other. So if I had sheets 1, 2 and 3 this would be grand but if I have sheets 1,3 and 500 it causes a #REF error. I didn't mention that the sheet numbers would be random (still in order smalest to largest though) as the basic formula works and I assumed the solution would work in the same way.

Appriciate the time taken though to get as far as you have with a better solution than I managed.

[Tony Valko]

If the number of sheets to include in the calculation varies create a dynamic range that refers to the sheet names.

Data Range

	A	B
1	Sheets	Sum
2	1	22514
3	3
4	4
5
6
7
8
9
10

Let's assume the sheet names will be in the range A2:A10 but the number of sheets varies.

Create this dynamic range:

Name: Sheets
Refers to: =$A$2:INDEX($A$2:$A$10,MATCH(1E100,$A$2:$A$10))

Then, the sum formula becomes:

=SUMPRODUCT(N(INDIRECT(Sheets&"!A1")))

Note that the sheet names must be entered in a contiguous range but can also be entered in any order.

[Harribone]

Thanks for this I will give it a go to see if this will work for my data, just so I understand though what is the N() function doing?
Never used it before and it looks like it is similar to VALUE()? Always looking to expand my knowledge, rather learn the function than just accept it.


EDIT
Tried and tested this. Does the job I need perfectly.
This will save people at work creating a long winded =Sheet1!A1+Sheet2!A1.... style formula which is annoying to edit when sheets are added removed. Plus it looks messy.

[Tony Valko]

what is the N() function doing? ...it looks like it is similar to VALUE()?

Yes, similar but not exactly the same.

N(...) returns the numeric value of its argument. However, if the argument is a text entry N(...) returns 0 where VALUE(...) returns the #VALUE! error. We need the N(...) function in this application to convert the results of the INDIRECT function into numeric numbers.

Tried and tested this. Does the job I need perfectly.

Good deal. Thanks for the feedback!