INDIRECT and horizontal reference to vertical fill

[KTJo4]

I have a large spreadsheet with several sheets. Sheet1-Sheet5, have data running across horizontally on every other column (a1, c1, e1, etc.). On Sheet6, cell A1, I need to calculate the sum of A1 on all previous sheets. However, I need to drag the formula down so that A2 calculates the sum of C1 on all previous sheets, A3=sum of E1, and so on. I believe I need to use the Indirect function but I'm not familiar enough with it to get it formatted correctly.

Thanks in advance!

 

[James006]

Hi,

You could test in sheet6 cell A1 : =SUM(Sheet1:Sheet5!A1)

HTH

 

[KTJo4]

Thank you for your reply. However, for the sake of asking this question I simplified the specifics of the spreadsheet. I actually have 20 sheets, but only need to reference 5 of them, and they are not actually named Sheet1, Sheet2, etc; nor are they adjacent to eachother. Additionally, I still need to be able to drag the formula down so that it is referencing across every 2nd column.

 

[James006]

Hi,

I do not know how familiar you are with User Defined Function (UDF) ... but given your specific constraints, it would be much more flexible ...

As far as the adjustment for every 2nd column ... you could test =ADDRESS(1,(ROW()*2)-1,4) starting on row 1 ...

HTH

 

[James006]

Hi again,

Within your Sheet 6 , you could list all the various Sheet Names concerned ... say in range G1:G5

then in cell A1 , you could test fomula : =SUMPRODUCT(N(INDIRECT("'"&$G$1:$G$5&"'!"&ADDRESS(1,(ROW()*2)-1,4))))

HTH