| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Solution Required to prevent #Ref! when rows deleted
Hi All,
I have a spreadsheet where on sheet 1 I have some data and in sheet 2 I have a template that uses simple =Sheet1!A1 formulas. The problem I have is that if I delete row A on sheet 1, the formula on sheet 2 that used to say =Sheet1!A1 turns to =Sheet1!'REF! How do I change this so that I can happily delete rows from Sheet 1 without messing up my template. I have tried making the formulas absolute and this doesn't work? Any suggestions?????? Ta Andi |
|
#2
|
|||
|
|||
|
1)
=INDIRECT("Sheet1!A1") 2) =INDEX(Sheet1!A:A,1) The 2nd is less expensive. Andibevan wrote: Hi All, I have a spreadsheet where on sheet 1 I have some data and in sheet 2 I have a template that uses simple =Sheet1!A1 formulas. The problem I have is that if I delete row A on sheet 1, the formula on sheet 2 that used to say =Sheet1!A1 turns to =Sheet1!'REF! How do I change this so that I can happily delete rows from Sheet 1 without messing up my template. I have tried making the formulas absolute and this doesn't work? Any suggestions?????? Ta Andi -- [1] The SumProduct function should implicitly coerce the truth values to their Excel numeric equivalents. [2] The lookup functions should have an optional argument for the return value, defaulting to #N/A in its absence. |
|
#3
|
|||
|
|||
|
Thanks - what do you mean by less expensive? Are you referring to system
resources? "Aladin Akyurek" wrote in message ... 1) =INDIRECT("Sheet1!A1") 2) =INDEX(Sheet1!A:A,1) The 2nd is less expensive. Andibevan wrote: Hi All, I have a spreadsheet where on sheet 1 I have some data and in sheet 2 I have a template that uses simple =Sheet1!A1 formulas. The problem I have is that if I delete row A on sheet 1, the formula on sheet 2 that used to say =Sheet1!A1 turns to =Sheet1!'REF! How do I change this so that I can happily delete rows from Sheet 1 without messing up my template. I have tried making the formulas absolute and this doesn't work? Any suggestions?????? Ta Andi -- [1] The SumProduct function should implicitly coerce the truth values to their Excel numeric equivalents. [2] The lookup functions should have an optional argument for the return value, defaulting to #N/A in its absence. |
|
#4
|
|||
|
|||
|
INDIRECT affects performance/speed.
Andibevan wrote: Thanks - what do you mean by less expensive? Are you referring to system resources? "Aladin Akyurek" wrote in message ... 1) =INDIRECT("Sheet1!A1") 2) =INDEX(Sheet1!A:A,1) The 2nd is less expensive. Andibevan wrote: Hi All, I have a spreadsheet where on sheet 1 I have some data and in sheet 2 I have a template that uses simple =Sheet1!A1 formulas. The problem I have is that if I delete row A on sheet 1, the formula on sheet 2 that used to say =Sheet1!A1 turns to =Sheet1!'REF! How do I change this so that I can happily delete rows from Sheet 1 without messing up my template. I have tried making the formulas absolute and this doesn't work? Any suggestions?????? Ta Andi -- [1] The SumProduct function should implicitly coerce the truth values to their Excel numeric equivalents. [2] The lookup functions should have an optional argument for the return value, defaulting to #N/A in its absence. -- [1] The SumProduct function should implicitly coerce the truth values to their Excel numeric equivalents. [2] The lookup functions should have an optional argument for the return value, defaulting to #N/A in its absence. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| formula to use when number of rows changes dynamically | Excel Worksheet Functions | |||
| how do i prevent a #REF! error when i delete non adjacent rows? | Excel Worksheet Functions | |||
| REALLY miussing rows in Excel2000 | Excel Discussion (Misc queries) | |||
| Hiding Rows if the linked rows are blank | Excel Discussion (Misc queries) | |||
| Adding Rows to Master Sheet | New Users to Excel | |||
Linear Mode
