using row number or column letters in formula

[Chris]

Now I'm trying to use a row number in a formula so I don't have to type the
same formula individually on each row 12 times!
My formula is currently this
=SUM(INDIRECT("'"&'Control Sheet'!$F$57&"'!G7")+INDIRECT("'"&'Control
Sheet'!$F$58&"'!G7")+INDIRECT("'"&'Control
Sheet'!$F$59&"'!G7")+INDIRECT("'"&'Control
Sheet'!$F$60&"'!G7")+INDIRECT("'"&'Control
Sheet'!$F$61&"'!G7")+INDIRECT("'"&'Control
Sheet'!$F$62&"'!G7")+INDIRECT("'"&'Control
Sheet'!$F$63&"'!G7")+INDIRECT("'"&'Control Sheet'!$F$64&"'!G7"))
The formula pulls the value of one cell in each worksheet and totals them.

I normal just drag the formula across and down and it will change G7 to H7
across, G7 to G8 down and so on, but now that I've added the INDIRECT
function dragging doesn't work.

I am thinking that I could change the location of the cell G7 to G(row
number) where row number is the same as the row I am entering the formula
in.

If I can do this with row number, is it possible with column letter and what
if column letter would not always be the same as the column I am in?

Thanks as always
Chris

 

[Ashish Mathur]

Hi,

If you wish to sum the value in the same cell of the some worksheets, get
all the worksheets together and then use

=sum(first:last!G7)

First is the tab of the first worksheet from where you want to pick up the
data
Last is the tab of the last worksheet from where you want to pick up the
data

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Chris]

Oh, that looks interesting, thanks. I've not heard of that before and could
place those sheets next to each other so I'm going to try it.

Regards

Chris

 

[Chris]

Would you tell me the format please? I've tried this

=SUM('Sheet 1'!:'Sheet 6'!G7) and I've tried =SUM('Sheet 1'!G7:'Sheet 6'!G7)

the first one doesn't work at all and the second returns the #VALUE! error

 

[Dave Peterson]

I'd try:

=sum('sheet 1:sheet 6'!g7)

Actually, I'd add a couple of sheets to the outside of this grouping and call
them Start and End and use:

=sum('start:end'!g7)

Then I could add (or subtract) sheets from this "sandwich" to play what-if
games--or even add new sheets and not have to adjust the formula.

If you get a #value! error with these, then look at the data in G7 of each of
the sheets. I'd expect an error in at least one of the cells.

 

[Chris]

OMG - it works! Thank you loads, you probably saved me 2 hours work but
better than that, you've taught me something that i can use all over the
place. Also, your tip about xtra sheets is impressive if only because it's
so simple.

Thanks again
Chris