Named ranges

[Guest]

I have an Excel 2003 workbook with multiple named ranges. They all begin with
LF followed by the specific name (LFData, LFStuff, LFFoot, etc.) and are
found on various sheets in the workbook. Is it possible to create a function
to gather all of the LF named ranges and give me a total? I don't want to
individually put the named ranges in, I want Excel to do it for me. Based on
my example above it would find for me LFName, LFStuff, LFFoot and show me the
total of all three of these. However, I have more like 20 of these named
ranges.

If LFData = 5 and LFStuff = 1 and LFFoot = 2 the formula would return the
answer 8.

Thanks for the help. : )

 

[Dave Peterson]

Maybe you can define another name?

Insert|Name|Define
Names in Workbook:
LFAll

Refers to:
=LFData,LFStuff,LFFoot,.....

Then you could use:
=sum(lfAll)

If all you ever wanted was to sum that range, you could use:

Insert|Name|Define
Names in Workbook:
LFSum

Refers to:
=sum(LFData,LFStuff,LFFoot,.....)

Then just use:
=LFSum
in a cell.

 

[Guest]

Try this small macro:

Sub rangerover()
v = 0
Dim n As Name
For Each n In ActiveWorkbook.Names
v = v + Evaluate("=SUM(" & n.Name & ")")
Next
MsgBox (v)
End Sub

It will add up the values in all the Named Ranges in a workbook. It will
handle ranges that include more than one cell.

If you are not familiar with VBA:

1. ALT-F11 to bring up the VBE window
2 ALT-I
ALT-M to bring up a fresh module
3. paste the stuff in and close the VBE window

To execute:
Tools > Macro > Macros...
and select run.