Calculating range names for sum() function

[Kevin Sprinkel]

I have a table that displays summary information as
follows. catA1, catA2, etc. are defined cell ranges:

1 2 3 4
Category A sum(catA1) sum(catA2) sum(catA3) sum(catA4)
Category B sum(catB1) sum(catB2) sum(catB3) sum(catB4)
Category C sum(catC1) sum(catC2) sum(catC3) sum(catC4)
Category D sum(catD1) sum(catD2) sum(catD3) sum(catD4)

Rather than type the range name explicitly, is there a
function that will calculate the argument range name and
pass it to the sum function, e.g, for Cat A, product 1:

Range Name = "Category" & <cellreferences> = "CategoryA1"
Cell Formula = sum(Range Name)

Thanks for any assistance.
Kevin Sprinkel
Becker & Frondorf

 

[Frank Kabel]

Hi Kevin
try using INDIRECT:
=SUM(INDIRECT("Category" & cellreference))

Frank

 

[Kevin Sprinkel]

Thanks, Frank. It's a beautiful thing...

 

[Bob Phillips]

Kevin,

Here's the first, you can just copy across and down

=SUM(INDIRECT("Category"&RIGHT($A2,1)&B$1)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Frank Kabel]

Kevin,

Here's the first, you can just copy across and down

=SUM(INDIRECT("Category"&RIGHT($A2,1)&B$1)

Bob
you're an perfectionist ;-)
Frank

 

[Bob Phillips]

How dare you, nobody accuses me of that <VG>!

Bob