reference a range based on a cell value

  • Thread starter Thread starter william kossack
  • Start date Start date
W

william kossack

I have a sheet where row 1 has names and the rest of the columns numbers.

for example
harry, bill, ann, kathy, and sparky

A program that I can not change pushes data into the spreadsheet. I'd
like to be able to calculate row totals for defined groups of names.

one day the columns might contain
harry, bill, ann, kathy and sparky
but the next day it might contain
harry, sam, bill, sparky, ken

The problem is as the list of names changes columns get added or taken
out of the sheet.

How can I set up formulas to compute the sum of rows for say
harry, bill, and sparky without having to redo the formulas each time?
 
On a separate sheet just input in A1

=IF(Sheet1!A1<>"",SUM(Sheet1!A:A),"")

and drag across as far as you are likely to ever need.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
With
On Sheet1 cells B1:E5 containing:

Harry Ann Bill Sparky
1 2 3 4
5 6 7 8
9 10 11 12
13 14 15 16

Try something like this:
On Sheet2
A1: Harry
B1: =SUMPRODUCT((Sheet1!$B$1:$E$1=A1)*Sheet1!$B$2:$E$5)

Enter other names under cell A1 and
copy the B1 formula down as far as you need

In the above example, B1 returns 28 (the sum of 1,5,9,13)

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP
 
Back
Top