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.
--
CG

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?
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"CEG" wrote:

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.
--
CG

No, there is no range name "above". Also, it is used multiple times in the
same spreadsheet.
--
CG


"Dave F" wrote:

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?
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"CEG" wrote:

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.
--
CG

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.
--
CG


"Dave F" wrote:

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?
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"CEG" wrote:

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.
--
CG

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.
"CEG" wrote in message
...
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.
--
CG


"Dave F" wrote:

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?
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"CEG" wrote:

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.
--
CG

That's a good point. It may well be a UDF.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Alan" wrote:

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.
"CEG" wrote in message
...
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.
--
CG


"Dave F" wrote:

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?
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"CEG" wrote:

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.
--
CG

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 InsertNameDefineAbove 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)
--
Regards

Roger Govier


"CEG" wrote in message
...
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.
--
CG


"Dave F" wrote:

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?
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"CEG" wrote:

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.
--
CG

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?
--
CG


"Roger Govier" wrote:

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 InsertNameDefineAbove 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)
--
Regards

Roger Govier


"CEG" wrote in message
...
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.
--
CG


"Dave F" wrote:

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?
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"CEG" wrote:

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.
--
CG

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.
"CEG" wrote in message
...
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?
--
CG


"Roger Govier" wrote:

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 InsertNameDefineAbove 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)
--
Regards

Roger Govier


"CEG" wrote in message
...
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.
--
CG


"Dave F" wrote:

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?
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"CEG" wrote:

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.
--
CG

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.

--
Regards

Roger Govier


"CEG" wrote in message
...
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?
--
CG


"Roger Govier" wrote:

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 InsertNameDefineAbove 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)
--
Regards

Roger Govier


"CEG" wrote in message
...
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.
--
CG


"Dave F" wrote:

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?
--
A hint to posters: Specific, detailed questions are more likely to
be
answered than questions that provide no detail about your problem.


"CEG" wrote:

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.
--
CG