Hi Roger,
Thanks for that, I had no idea that was possible. One to note for future
reference,
Regards,
Alan.
"Roger Govier" wrote in message
...
Hi Alan

No, it's by code only.

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

Names("Above").Visible = False


--
Regards

Roger Govier


"Alan" wrote in message
...
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.
"Roger Govier" wrote in message
...
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


--
Regards

Roger Govier


"Alan" wrote in message
...
Nice one Roger, but how do you hide a named range? I didn't know that
could be done,
Regards,
Alan.
"Roger Govier" wrote in message
...
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.


--
Regards

Roger Govier


"CEG" wrote in message
...
OK, duh, I found it. Will send shortly.
--
CG


"CEG" wrote:

Sorry, I'm not seeing your email address anywhere.
--
CG


"Roger Govier" wrote:

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