SUM Columns via INDIRECT formula

[mabbott]

Hi all,

I have come across this formula, INDIRECT, and think it may help me with the tedious task of returning the SUM of a column in a worksheet via a cell that names that worksheet. But for the life of me i cannot figure it out...any help would be much appreciated.

so...in sheet1 I have the name of the worksheets in cells A1 to C1, e.g A1=110121, B1=110122, C1=110123, in A2 I want the sum of column D:D in worksheet 110121 to be returned, this would also be the case for B1 & C1 but returning the value of the worksheet name above them...

I imagine that this fairly simple...but alas I cannot get my head around it!!!

Thanks in advance

Mick

 

[Andrew Poulsom]

Try:

=SUM(INDIRECT("'"&A1&"'!D:D"))

 

[jasonb75]

Try

=SUM(INDIRECT(A1&"!D:D"))

 

[Nogslaw]

Since those are numeric values, you'll need to use the apostrophe (') around the sheetname.

To set it directly, you could use =SUM('110121'!D:D)

To use the indirect function, you could use =SUM(INDIRECT(B2&"!D:D")), so long as the value in B2 is stored as text.

If the value in B2 is stored as a number, then you can use the following to convert it to text within the formula: =SUM(INDIRECT(TEXT(C2, "General")&"!D:D"))

 

[jasonb75]

Since those are numeric values, you'll need to use the apostrophe (') around the sheetname.

To set it directly, you could use =SUM('110121'!D:D)

To use the indirect function, you could use =SUM(INDIRECT(B2&"!D:D")), so long as the value in B2 is stored as text.

If the value in B2 is stored as a number, then you can use the following to convert it to text within the formula: =SUM(INDIRECT(TEXT(C2, "General")&"!D:D"))

Something I wasn't aware, I don't use numbers for sheet names, thanks for correcting my error.

 

[mabbott]

Thanks all, Andrew yes that worked perfectly...just out of interest though, what do each of the steps do, I understand the cell references but as for the "" and the & bits I am not 100% sure I just like to understand formulas fully before using them...

 

[Andrew Poulsom]

The steps construct a reference as a string, using concatenation. If A1 contains 110121, then the reference '110121'!D:D can be written:

"'"&A1&"'!D:D"

The bits in quotes are constant and a1 is variable.