Cell reference gets disturb after inserting or deleting row

Dear Friends,


I have a excel file with two worksheets. Sheet 1 is a input sheet and Sheet 2 is report sheet. The user enter the data in sheet 1 and sheet 2 generate a report based on numbers entered in sheet 1.

The cell in sheet 2 refer to cell in sheet 1 using formula e.g =Sheet1!B5

But if any user deleted the row or insert a new row (in sheet 1), the formula below that row get disturb (in sheet 2) and I get error #REF!

How can I have formula (e.g =Sheet1!B5) refer to same row/cell in sheet 2 even if any row is deleted or a new row is inserted in sheet 1

Thanks,
Rahul

use INDEX (over INDIRECT)

Formula:

Please Login or Register  to view this content.

the above assumes users are not deleting column B in it's entirety, if so you can expand the INDEX reference, e.g.:

Formula:

Please Login or Register  to view this content.

but bear in mind that it's not bullet proof... INDIRECT would be safer still, however it's Volatile so don't use it unless you have.

If you need the reference to 5 to be dynamic let us know how you need it to adapt over your formula range (i.e. increment).

Hi XLent,

Thanks for your reply. The first formula (=INDEX(Sheet1!$B:$B,5) works. But I wan the row reference to be dynamic, as I have aprox 5000 rows. Kindly suggest what I need to change in the formula.

you need to first elaborate as to

a) where the formula is being placed, and
b) how it is being dragged and/or needs to adapt

1-The formula i.e (=INDEX(Sheet1!$B:$B,5) is placed in sheet 2 in cell B5.

2- I copy the formula (Clt+C) and past it all the way down till row 5000

I hope I answered your question. Kindly let me know if you need any additional information.

Thanks

OK; as long as you're not deleting rows from Sheet2, only Sheet1, the below should suffice:

Formula:

Please Login or Register  to view this content.

Thank you! i was having this exact same problem!!

Thanks XLent.. your solution worked for me buddy!!