Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How Do I Avoid #REF! In Formulas?
I am using Microsoft Excel 2007.
My spreadsheet has two tabs, Sheet1 and Sheet2. In Sheet2 I have the following formula going down Col. A linking to data on Sheet1: ='Sheet1'!A15 ='Sheet1'!A16 etc. etc. But if I delete some rows on Sheet1, then the formula on Sheet2 becomes: ='Sheet1'!#REF! What do I need to do so that the dreaded #REF! does not happen when I delete rown on Sheet1? -- tb |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How Do I Avoid #REF! In Formulas?
On Thu, 7 Feb 2013 01:06:13 +0000 (UTC), "tb"
wrote: I am using Microsoft Excel 2007. My spreadsheet has two tabs, Sheet1 and Sheet2. In Sheet2 I have the following formula going down Col. A linking to data on Sheet1: ='Sheet1'!A15 ='Sheet1'!A16 etc. etc. But if I delete some rows on Sheet1, then the formula on Sheet2 becomes: ='Sheet1'!#REF! What do I need to do so that the dreaded #REF! does not happen when I delete rown on Sheet1? used named ranges In the upper left, where the cell name is declared. When you hover your cursor there, note that it shows up as an excel object called "name box". When you put your cursor there and give your data specific cell location an actual name, you can then refer to said name in your formula, instead of a hard cell location. Then, no matter what you do as far as rows and such, that cell location will always carry that assigned "range name", and that formula will always point there. It is a bit of data entry labor and cursor moves to do each cell involved, but done right, and you can still drag and carry your formula. (end your named range in a numeric figure the number of digits long you intend fill out) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How Do I Avoid #REF! In Formulas?
I strongly recommend using local scope when adding names, so name
conflicts don't occur when copying/moving the sheet to another workbook. You can force local scope by prefixing the range name with the sheetname as follows: '<sheetname'!RangeName -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#4
|
|||
|
|||
Hi tb
May be the INDIRECT & ADDRESS functions: =INDIRECT(ADDRESS(ROW(A15),1,4,1,"Sheet1")) Quote:
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How Do I Avoid #REF! In Formulas?
Il 07/02/2013 02:06, tb ha scritto:
I am using Microsoft Excel 2007. My spreadsheet has two tabs, Sheet1 and Sheet2. In Sheet2 I have the following formula going down Col. A linking to data on Sheet1: ='Sheet1'!A15 ='Sheet1'!A16 etc. etc. But if I delete some rows on Sheet1, then the formula on Sheet2 becomes: ='Sheet1'!#REF! What do I need to do so that the dreaded #REF! does not happen when I delete rown on Sheet1? Modify =Sheet1!A15 in =INDIRECT("Sheet1!A"&ROW(A15)) Hi, E. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How Do I Avoid #REF! In Formulas?
On 2/7/2013 at 2:07:57 AM Kevin@Radstock wrote:
Hi tb May be the INDIRECT & ADDRESS functions: =INDIRECT(ADDRESS(ROW(A15),1,4,1,"Sheet1")) tb;1609161 Wrote: I am using Microsoft Excel 2007. My spreadsheet has two tabs, Sheet1 and Sheet2. In Sheet2 I have the following formula going down Col. A linking to data on Sheet1: ='Sheet1'!A15 ='Sheet1'!A16 etc. etc. But if I delete some rows on Sheet1, then the formula on Sheet2 becomes: ='Sheet1'!#REF! What do I need to do so that the dreaded #REF! does not happen when I delete rown on Sheet1? -- tb It seems to be working. Thanks, Kevin. -- tb |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Avoid #div/0! | Excel Worksheet Functions | |||
How to avoid Compatibility Checker MsgBox? VBA to avoid or prevent? | Excel Programming | |||
How do I avoid referencing hidden values in formulas like OFFSET? | Excel Worksheet Functions | |||
what is this, how do I avoid it? | Excel Discussion (Misc queries) | |||
avoid div by zero | Excel Programming |