Excel Formula and Defining a name.

[willhub]

Hi.

I would like to know, if I had a cell that had a name defined to it, so "SavingsMoney"

Is there a formula I can use, that will find any cells within a range that is using "SavingsMoney" e.g. "=SUM(SavingsMoney)", and then add all of them together, so say if savings money was set to £50 and I put the =SUM formula in 3 boxes, I'd want the specific box that adds all of them up to show £150.

Thanks.

 

[dazzerd]

You could always try it?

But from memory if the defined named range is a continuous range then yes (as it is just treated as a 1d array) if not then no as even a cell is a range and you cannot have 2 ranges with the same name.

 

[Namsnik]

I don't think you can do that. Certainly not straightforwardly.
(If "SavingsMoney" is a single cell then
"=SavingsMoney" is a bit easier than "=SUM(SavingsMoney)")

If this is a one-off requirement then how about using Find&Replace to replace all instances of "SavingsMoney" with "SavingsMoney". That will tell you how many there are.

If this is an ongoing need then it would be possible to write a UserDefinedFunction to do what you want. There may also be add-ons out there that would help.