If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Delete ROW on Sheet#1 corupts data on Sheet#2
If I delete a ROW od data on sheet1, my formulas on sheet2 screwup.
a2 on sheet2: (Filled down to 200) =If('LIST'!$B2="Y","YES","NO") If I delete row #25 on sheet1, my row 25 on sheet2 has a #REF error Any ideas how I overcome this? Thanks |
#2
|
|||
|
|||
Delete ROW on Sheet#1 corupts data on Sheet#2
Duane wrote:
If I delete a ROW od data on sheet1, my formulas on sheet2 screwup. a2 on sheet2: (Filled down to 200) =If('LIST'!$B2="Y","YES","NO") If I delete row #25 on sheet1, my row 25 on sheet2 has a #REF error Any ideas how I overcome this? Thanks By chance, is sheet1 really named "LIST"? Maybe try this in "sheet2": =IF(INDIRECT("'LIST'!$B"&ROW())="Y","YES","NO") |
#3
|
|||
|
|||
Delete ROW on Sheet#1 corupts data on Sheet#2
Duane -
If you delete a row in the first sheet, then any cell in the second sheet referencing that cell loses the reference (hence the #REF). You can delete the CONTENTS of the cells in the row instead of deleting the row, and the references will remain. Since you didn't give us any information on the relationships between the two sheets or what you would like to happen, we can't recommend a potentially better solution. -- Daryl S "Duane" wrote: If I delete a ROW od data on sheet1, my formulas on sheet2 screwup. a2 on sheet2: (Filled down to 200) =If('LIST'!$B2="Y","YES","NO") If I delete row #25 on sheet1, my row 25 on sheet2 has a #REF error Any ideas how I overcome this? Thanks |
#4
|
|||
|
|||
Delete ROW on Sheet#1 corupts data on Sheet#2
Thanks! That works excatly like I was trying to do!
Now if I can only figure out why INDEX keeps looking at a cell on the sheet I have the formula on and NOT the sheet where I want the info from!!! =INDEX(LIST!F:F,F2) looks at F2 on this sheet, not LIST sheet =INDEX(LIST!F:F,) appears to give me the right answer but I have more reading to do on INDEX to understand why it works!! Thanks again guys, great work!!! "Glenn" wrote: Duane wrote: If I delete a ROW od data on sheet1, my formulas on sheet2 screwup. a2 on sheet2: (Filled down to 200) =If('LIST'!$B2="Y","YES","NO") If I delete row #25 on sheet1, my row 25 on sheet2 has a #REF error Any ideas how I overcome this? Thanks By chance, is sheet1 really named "LIST"? Maybe try this in "sheet2": =IF(INDIRECT("'LIST'!$B"&ROW())="Y","YES","NO") . |
#5
|
|||
|
|||
Delete ROW on Sheet#1 corupts data on Sheet#2
Awful waste of my time and effort replying to your original post on same
subject. Remember Kub Kar List question? Gord Dibben MS Excel MVP On Thu, 11 Feb 2010 09:11:01 -0800, Duane wrote: If I delete a ROW od data on sheet1, my formulas on sheet2 screwup. a2 on sheet2: (Filled down to 200) =If('LIST'!$B2="Y","YES","NO") If I delete row #25 on sheet1, my row 25 on sheet2 has a #REF error Any ideas how I overcome this? Thanks |
Thread Tools | |
Display Modes | |
|
|