using indirect to sum range
 

I have a sheet that references data in another sheet within a workbook
in its same corresponding row. For example, for an item in Sheet1 in
row 6 the formula might be:

=Sheet2!F6+Sheet2!G6

Due to the way that the data is loaded (this is part of a complicated
process that I cannot change and I only am able to make updates to my
own sheet) I cannot just define the cell in row 6 to be =Sheet2!
F6+Sheet2!G6 but I can get my desired result by using:

=Indirect("Sheet2!F"&row())+Indirect("Sheet2!G"&ro w())

However, I cannot figure out how to do this when my formula should be
something like:

=Sum(Sheet2!B6:E6)

Any ideas?

using indirect to sum range
 

Have you tried:

=SUM(INDIRECT("Sheet2!B"&row()&":E"&row()))

?

Hope this helps.

Pete

On Sep 17, 4:18 pm, Edward wrote:
I have a sheet that references data in another sheet within a workbook
in its same corresponding row. For example, for an item in Sheet1 in
row 6 the formula might be:

=Sheet2!F6+Sheet2!G6

Due to the way that the data is loaded (this is part of a complicated
process that I cannot change and I only am able to make updates to my
own sheet) I cannot just define the cell in row 6 to be =Sheet2!
F6+Sheet2!G6 but I can get my desired result by using:

=Indirect("Sheet2!F"&row())+Indirect("Sheet2!G"&ro w())

However, I cannot figure out how to do this when my formula should be
something like:

=Sum(Sheet2!B6:E6)

Any ideas?

using indirect to sum range
 

On Sep 17, 11:28 am, Pete_UK wrote:
Have you tried:

=SUM(INDIRECT("Sheet2!B"&row()&":E"&row()))

?

Hope this helps.

Pete

On Sep 17, 4:18 pm, Edward wrote:



I have a sheet that references data in another sheet within a workbook
in its same corresponding row. For example, for an item in Sheet1 in
row 6 the formula might be:

=Sheet2!F6+Sheet2!G6

Due to the way that the data is loaded (this is part of a complicated
process that I cannot change and I only am able to make updates to my
own sheet) I cannot just define the cell in row 6 to be =Sheet2!
F6+Sheet2!G6 but I can get my desired result by using:

=Indirect("Sheet2!F"&row())+Indirect("Sheet2!G"&ro w())

However, I cannot figure out how to do this when my formula should be
something like:

=Sum(Sheet2!B6:E6)

Any ideas?- Hide quoted text -

- Show quoted text -

Clearly not because this works!

Thanks.

using indirect to sum range
 

You're welcome, Edward - thanks for feeding back.

I wasn't sure I understood what the problem was.

Pete

On Sep 17, 4:30 pm, Edward wrote:
On Sep 17, 11:28 am, Pete_UK wrote:





Have you tried:

=SUM(INDIRECT("Sheet2!B"&row()&":E"&row()))

?

Hope this helps.

Pete

On Sep 17, 4:18 pm, Edward wrote:

I have a sheet that references data in another sheet within a workbook
in its same corresponding row. For example, for an item in Sheet1 in
row 6 the formula might be:

=Sheet2!F6+Sheet2!G6

Due to the way that the data is loaded (this is part of a complicated
process that I cannot change and I only am able to make updates to my
own sheet) I cannot just define the cell in row 6 to be =Sheet2!
F6+Sheet2!G6 but I can get my desired result by using:

=Indirect("Sheet2!F"&row())+Indirect("Sheet2!G"&ro w())

However, I cannot figure out how to do this when my formula should be
something like:

=Sum(Sheet2!B6:E6)

Any ideas?- Hide quoted text -

- Show quoted text -

Clearly not because this works!

Thanks.- Hide quoted text -

- Show quoted text -

using indirect to sum range
 

Nel ups.com,
Edward ha scritto:
I have a sheet that references data in another sheet within a workbook
in its same corresponding row. For example, for an item in Sheet1 in
row 6 the formula might be:

=Sheet2!F6+Sheet2!G6

Due to the way that the data is loaded (this is part of a complicated
process that I cannot change and I only am able to make updates to my
own sheet) I cannot just define the cell in row 6 to be =Sheet2!
F6+Sheet2!G6 but I can get my desired result by using:

=Indirect("Sheet2!F"&row())+Indirect("Sheet2!G"&ro w())

However, I cannot figure out how to do this when my formula should be
something like:

=Sum(Sheet2!B6:E6)

Any ideas?

Hi Edward,

Should be something like this:

=Sum(Indirect("Sheet2!B"&row()&":E"&row()))

(written directly here, so some test is needed...)


--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.


Thanks in advance for your feedback.


Ciao


Franz Verga from Italy