Clever Geek Handbook

Delete rows in excel without affecting Excel formula

I have a table where Iam is trying to add serial numbers on each row using the below formula

= IF (C149 & Lt;> "; MAX ($ B $ 149: OFFSET (B150; -1; 0)) + 0.1;" ")

However, whenever I delete rows in column B, all other rows will show the error value like # N / A

Can I delete rows without affecting the formula?

I heard there is an excel "INDEX" function to be used, please answer with your answers how to apply the INDEX function to the above formula

+3


source to share


2 answers


The problem may be related to $B$149

when you delete a row, the value of $ means that the 149 can not appropriately be changed as C149

and B150

it may mean that it refers to the wrong data after deletion.



0


source


Not the answer you asked for, but google chooses this to remove without breaking the formalities - so someone lands here from Google ...

Formulas break because you delete cells from the spreadsheet - by dragging and dropping the sheet structure. Where as you want to do, just delete the data and all other data will move up.



One way is to select all of the data below the line you want to clear (plus one blank line at the end) - then copy that range (to the clipboard) and paste everything back onto one line above.

Not a great solution - but it works - I found this question looking for a better way! :)

+1


source






More articles:


All Articles