CODEWITHSUNDEEP
excelrefexcel-indirect
Ken Poole
Ken Poole

Reputation: 11

Prevent #ref when cell deleted

I'm importing a text file into one sheet, eliminating un-needed lines, while formatting the data on the second sheet.

I have all the formulas on the second sheet set up, referencing cells on the first sheet.

The problem is that after deleting the un-needed lines and the data moves up, the formulas for the deleted lines say #ref. I want the formulas to use, say Sheet1!B2 no matter what data is there.

I see that Indirect works, so the next question is, how can I copy the Indirect function down a colum of 500 rows with each one having the right reference?

Thanks

Upvotes: 0

Views: 3546

Answers (2)

Jack Deeth
Jack Deeth

Reputation: 3357

If you only need to reference cells on the same row, including cells on different sheets on the same row, there's an easier way than Indirect.

If you write in A42:

=@B:B

it will look up B42.

If you write in A42:

='Sheet 2'!@X:X

It will look up X42 on Sheet 2.

If you delete the top rows on Sheet 2, the formula on Sheet 1 will point to the new X42 - no #REF! errors.

As long as your formulas in Sheet 1 just need to reference cells on the same row in Sheet 2 - so the formula in 'Sheet 1'!A42 might want 'Sheet 2'!X42 but not 'Sheet 2'!X43 - you can just put the column names as inputs into the formula. Otherwise you'll need Indirect.

For bonus points, name the columns in Sheet 2, so instead of ='Sheet 2'!@X:X you could write =cust_DateOfBirth for example.

Upvotes: 2

Casey Tuna Bloodworth
Casey Tuna Bloodworth

Reputation: 35

I had this same problem recently so just passing along the answer I got. Index functions will help. Below you can see one where the sheet name is reference in cell a1 and it will return the value in cell a2 of the corresponding sheet.

=INDEX(INDIRECT($A1&"!2:2"),COLUMN(A:A))

Upvotes: 2

Related Questions