Inserting rows in one sheet messes up formula in another sheet

[RammsteinNicCage]

I have two worksheets. The first sheet is where we enter data. The second sheet has a formula to read that data and basically format it for use in a chart. When we insert X new rows at the bottom of our data on the first sheet, it causes the formula to jump X rows so it basically skips X rows of new data.

Here's the formula:

=IF(ISNUMBER('Table 5'!BP93), 'Table 5'!BP93, NA())

To make it a little clearer, if I insert 10 new rows on the Table 5 sheet after row 93, then my formula on the other sheet (which I already have dragged down to row 120 for "automatic" updating) will go from 93 to 103, skipping the numbers in between.

Thanks for the help!

 

[pboltonchina]

Try this

=IF(ISNUMBER('Table 5'!$BP$93), 'Table 5'!$BP$93, NA())

 

[RammsteinNicCage]

Try this

=IF(ISNUMBER('Table 5'!$BP$93), 'Table 5'!$BP$93, NA())

I think that will make it so it always refers to row 93, right? It should be that worksheet 2 row 93 refers to the worksheet Table 5, row 93. Worksheet 2 row 94 refers to Table 5 row 94, etc.

 

[CaseyP]

I'm not sure what formatting (if any) you have in this, but your formulas on the sheet that messed up when you deleted from your other sheet...all you need to do is go a row above the error, copy the formulas in that row and fill down however far you need.

It is #REF!/#VALUE!'ing because you deleted/inserted and 'confused' it if you will. All rows above it should be good to go.

 

[RammsteinNicCage]

I'm not sure what formatting (if any) you have in this, but your formulas on the sheet that messed up when you deleted from your other sheet...all you need to do is go a row above the error, copy the formulas in that row and fill down however far you need.

It is #REF!/#VALUE!'ing because you deleted/inserted and 'confused' it if you will. All rows above it should be good to go.

Casey, that is what I have been doing now, but I'm trying to make it easier for my co-workers. The less I have to explain to them, the better.

With the way that my Table 5 sheet is set up, there are "foot notes" below the data (they will not fit in the footer because of the character limit and an image of them just doesn't look right and isn't as easy to edit), that is why we have to insert new rows between the last row of data and the foot notes whenever we get new data in.