|
=SUM(A1:ABOVE)
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 |
=SUM(A1:ABOVE)
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 |
=SUM(A1:ABOVE)
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 |
=SUM(A1:ABOVE)
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 |
=SUM(A1:ABOVE)
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 |
=SUM(A1:ABOVE)
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 |
=SUM(A1:ABOVE)
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 |
=SUM(A1:ABOVE)
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 |
=SUM(A1:ABOVE)
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 |
=SUM(A1:ABOVE)
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 |
=SUM(A1:ABOVE)
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 |
=SUM(A1:ABOVE)
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 |
=SUM(A1:ABOVE)
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 |
=SUM(A1:ABOVE)
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 |
=SUM(A1:ABOVE)
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 |
=SUM(A1:ABOVE)
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 |
=SUM(A1:ABOVE)
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 |
=SUM(A1:ABOVE)
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 |
| All times are GMT +1. The time now is 03:15 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com