using row number or column letters in formula
 

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

using row number or column letters in formula
 

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" wrote in message
...
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

using row number or column letters in formula
 

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

"Ashish Mathur" wrote in message
...
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" wrote in message
...
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

using row number or column letters in formula
 

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

"Ashish Mathur" wrote in message
...
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" wrote in message
...
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

using row number or column letters in formula
 

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 wrote:

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

"Ashish Mathur" wrote in message
...
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" wrote in message
...
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


--

Dave Peterson

using row number or column letters in formula
 

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

"Dave Peterson" wrote in message
...
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 wrote:

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

"Ashish Mathur" wrote in message
...
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" wrote in message
...
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


--

Dave Peterson