Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
deleting rows messing up formulas
I have a workbook that in Sheet3 has a cell with the following formula
=IF(ISBLANK(Sheet1!$A$16),"-",Sheet1!$A$16) Anyway if I delete row 16 on Sheet 1 and everything else shifts up then this formula becomes =IF(ISBLANK(Sheet1!#REF!),"-",Sheet1!#REF!) Is there anyway to prevent this and make it just pull the data that is now in Sheet1 A16 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
deleting rows messing up formulas
On Jan 30, 3:31*pm, Chad wrote:
I have a workbook that in Sheet3 has a cell with the following formula =IF(ISBLANK(Sheet1!$A$16),"-",Sheet1!$A$16) Anyway if I delete row 16 on Sheet 1 and everything else shifts up then this formula becomes =IF(ISBLANK(Sheet1!#REF!),"-",Sheet1!#REF!) Is there anyway to prevent this and make it just pull the data that is now in Sheet1 A16 You could try using the OFFSET function: =IF(ISBLANK(OFFSET(Sheet1!A1,15,0)),"-",OFFSET(Sheet1!A1,15,0)) The OFFSET function takes a reference position and then specifies a number of rows and columns to offset from ie in this case, from cell A1 offset 15 rows down and zero rows across. Doing it this way allows you to delete rows and it should still be OK. HTH Murray |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
deleting rows messing up formulas
"Murray" wrote: You could try using the OFFSET function: =IF(ISBLANK(OFFSET(Sheet1!A1,15,0)),"-",OFFSET(Sheet1!A1,15,0)) The OFFSET function takes a reference position and then specifies a number of rows and columns to offset from ie in this case, from cell A1 offset 15 rows down and zero rows across. Doing it this way allows you to delete rows and it should still be OK. HTH Murray You are a genius and I love you to no end you have no idea. All my excel questions go here from now on. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Question about deleting rows and formulas | Excel Discussion (Misc queries) | |||
Sort without messing up formulas | Excel Discussion (Misc queries) | |||
Sort & add columns without messing up formulas | Excel Worksheet Functions | |||
Rows containing "#N/A" are messing with my formulas, please help | Excel Worksheet Functions | |||
deleting rows--calculating formulas with each row as it goes? | Excel Discussion (Misc queries) |