adding cell names

S

sadath

Active Member

Joined

Oct 10, 2004

Messages

268

Office Version

1. 365

Platform

1. Windows

• Dec 4, 2006

• #2

adding cell name

I have named some random cells as name1, name2.... name5

to add these cells, the formula is not working

=SUMPRODUCT(INDIRECT("name"&{"1","2","3","4","5"}))

 

Upvote 0

Richard Schollar

MrExcel MVP

Joined

Apr 19, 2005

Messages

23,707

• Dec 4, 2006

• #3

Hi Sadath

I can't seem to get this to work either. Maybe someone else will have an idea how to. Do you need to have different names for all these cells - or could you just call all of them "Name1" say (ie select all the relevant cells and then go Insert>Name>Define and give them a name)?

 

Upvote 0

Jon von der Heyden

MrExcel MVP, Moderator

Joined

Apr 6, 2004

Messages

10,912

Office Version

1. 365

Platform

1. Windows

• Dec 4, 2006

• #4

How are the defined ranges defined? Perhaps this may shed some light: http://www.mrexcel.com/board2/viewtopic.php?t=189749&highlight=indirect

 

Upvote 0

Richard Schollar

MrExcel MVP

Joined

Apr 19, 2005

Messages

23,707

• Dec 4, 2006

• #5

Hmm, following my own link in the link provided by Jon, it seems that Domenic (MrExcel formula wizard) has also had problems with a similar situation.

Fascinating...

 

Upvote 0

K

Krishnakumar

Well-known Member

Joined

Feb 28, 2003

Messages

2,615

• Dec 4, 2006

• #6

Somethink like this

=SUMPRODUCT(SUMIF(INDIRECT("name"&ROW(INDIRECT("1:5"))),{">0","<0"}))

should work.

 

Upvote 0

G

GlennUK

Well-known Member

Joined

Jul 8, 2002

Messages

11,871

Office Version

1. 2019
2. 2013

Platform

1. Windows

• Dec 4, 2006

• #7

How about:

Code:

=SUMPRODUCT(N(INDIRECT("name"&{"1","2","3","4","5"})))

 

Upvote 0

Richard Schollar

MrExcel MVP

Joined

Apr 19, 2005

Messages

23,707

• Dec 4, 2006

• #8

I still get a #REF with that formula Kris

 

Upvote 0

K

Krishnakumar

Well-known Member

Joined

Feb 28, 2003

Messages

2,615

• Dec 4, 2006

• #9

So this..

=SUM(N(INDIRECT("name"&{"1","2","3","4","5"})))

 

Upvote 0

Richard Schollar

MrExcel MVP

Joined

Apr 19, 2005

Messages

23,707

• Dec 4, 2006

• #10

Of course, it does help to actually have a cell named "Name1" when running the formula. Worked like a charm Kris

EDIT: Glenn - your formula was a thing of beauty too

 

Upvote 0