Vary sheet name & sum across multiple sheets
 

Hi,

I am using the formula =SUM('1:31'!K51) to get sum across multiple
sheets.

In cell
A1= 1
A2= 31

I want something like.

=SUM('cell(A2):cell(A2)'!K51) in which I can vary values in cells A1 &
A2.
If A1 & A2 are same, it should return one value instead of sum.

Vary sheet name & sum across multiple sheets
 

=SUMPRODUCT(N(INDIRECT("'"&ROW(INDIRECT(""&A1&":"& A2))&"'!K51")))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Sinner" wrote in message
...
Hi,

I am using the formula =SUM('1:31'!K51) to get sum across multiple
sheets.

In cell
A1= 1
A2= 31

I want something like.

=SUM('cell(A2):cell(A2)'!K51) in which I can vary values in cells A1 &
A2.
If A1 & A2 are same, it should return one value instead of sum.

Vary sheet name & sum across multiple sheets
 

On Mar 5, 12:47*am, "Bob Phillips" wrote:
=SUMPRODUCT(N(INDIRECT("'"&ROW(INDIRECT(""&A1&":"& A2))&"'!K51")))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Sinner" wrote in message

...



Hi,

I am using the formula =SUM('1:31'!K51) to get sum across multiple
sheets.

In cell
A1= 1
A2= 31

I want something like.

=SUM('cell(A2):cell(A2)'!K51) in which I can vary values in cells A1 &
A2.
If A1 & A2 are same, it should return one value instead of sum.- Hide quoted text -

- Show quoted text -

Hi Bob,
Worked like a charm : )
I wanna return blank in case both A1 & A2 are blank?


Thx again man.

Vary sheet name & sum across multiple sheets
 

=IF(OR(A1="",A2=""),"",SUMPRODUCT(N(INDIRECT("'"&R OW(INDIRECT(""&A1&":"&A2))&"'!K51"))))


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Sinner" wrote in message
...
On Mar 5, 12:47 am, "Bob Phillips" wrote:
=SUMPRODUCT(N(INDIRECT("'"&ROW(INDIRECT(""&A1&":"& A2))&"'!K51")))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Sinner" wrote in message

...



Hi,

I am using the formula =SUM('1:31'!K51) to get sum across multiple
sheets.

In cell
A1= 1
A2= 31

I want something like.

=SUM('cell(A2):cell(A2)'!K51) in which I can vary values in cells A1 &
A2.
If A1 & A2 are same, it should return one value instead of sum.- Hide
quoted text -

- Show quoted text -

Hi Bob,
Worked like a charm : )
I wanna return blank in case both A1 & A2 are blank?


Thx again man.