=SUM(A1:ABOVE)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Someone just sent me a spreadsheet using this formula:
=SUM(A1:ABOVE).

It's great because if you delete the row above it (which was the bottom of
the SUM range), your formula is still valid.

Unfortunately, I can only use it in a new spreadsheet by copying it from the
one that was sent to me and even then I sometimes can't resolve the #VALUE
issues. I can't find anything about this syntax in the help menu or in the
discussion threads. Is it new? Any info would be helpful.
 
It sounds like ABOVE is a named range?

Check the named range list in the drop down box to the left of the formula
bar. Do you see a named range called ABOVE?
 
No, there is no range name "above". Also, it is used multiple times in the
same spreadsheet.
 
After more experimenting, it does act somewhat like a named range. For
example, row 30 totals rows 2-29 in columns B:G using the formula
=SUM(B2:ABOVE), etc. and gives accurate totals.

However, if you select cell D10 and create the range name "above", you get
these values (formula doesn't change, just the results):

B30 = B2:D10
C30 = C2:D10
D30 = D2:D10
E30 = D2:E10
F30 = D2:F10
G30 = D2:G10

Then, if you delete the range name "above", all the sums go to #VALUE! error.
 
If it's not a named range it sounds like it may a a UDF (User Defined
Function). When you open the file do you get the 'Enable Macro's' prompt? If
so try hitting Alt and F11 to open the VB editor and open all the folders
on the left to see if there's any code in them. If there is copy and paste
it into a post and someone will probably be able to explain.
Regards,
Alan.
 
Hi

Using a named formula called Above can be a useful way of always
totalling to a given point, allowing for row insertions

If you Insert>Name>Define>Above Refers to
=INDEX(Sheet1!A:A,ROW()-1)

Then you will get exactly what you are describing

However, you may find the less preferable volatile version has been used
in your workbook
=OFFSET(Sheet1!A1,-1,0)
 
Thanks everyone for your replies. When I open the file, I do not get a macro
warning. When I go to edit macros, none are listed. However, if I open VBA,
there is a folder which is password protected called "funcres.xla". No code
in any of the other folders. I tried closing Excel & re-opening it, then
looking at the code (with no files open). The "funcres.xla" is still there,
but I still cannot use "above" in a new worksheet.

There are no named ranges.

Any other ideas?
 
Funcres.xla is an add in to do with the Analysis Toolpack and won't have
anything to do with your query I don't think. Short of looking at the file
I'm out of ideas,
Sorry,
Alan.
 
Hi
As Alan has said, funcres.xla is part of the Analysis Toolpak and is not
affecting your situation.
If you want to send me a copy of the file direct, I will take a look and
see if I can see what is happening.

To send direct remove NOSPAM form my address.
 
Hi Connie

File returned with hidden names now all visible.

For the benefit of others the names had all been hidden before the
workbook was saved.
I unhid them with this short piece of code

Sub unhideNames()
Dim n as Name
For each n in Names
n.visible = True
Next
End Sub

With sheets named (1), (2) etc. and cursor placed in A1, defined name
returned

ABOVE = '(1) '!A65536
BELOW = '(1) '!A2
RIGHT = '(1) '!B1
LEFT = '(1) '!IV1

Quite a neat way of automatically referring to cells to create an
effective range which grows as you insert rows or columns.
 
Nice one Roger, but how do you hide a named range? I didn't know that could
be done,
Regards,
Alan.
 
Hi Alan

Just reverse the property in the procedure posted

Sub hideNames()
Dim n as Name
For each n in Names
n.visible = False
Next
End Sub
 
I see, can this only be done by code? I don't know of any worksheet
procedure to this, is there one? This is intriguing!
Regards,
Alan.
 
Hi Alan

No, it's by code only.

You could do individual Names in the Immediate panel of the VBE

Names("Above").Visible = False
 
Hi Roger,
Thanks for that, I had no idea that was possible. One to note for future
reference,
Regards,
Alan.
 
Back
Top