On sheet 1 cell A2 references the same cell in sheet 2 and i want it to reference that location always, so if i Delete the row on the sheet that is being referenced i wont get a !REF# on my Sheet 1. how do i have the cell reference the location not the data. Sorry if this is unclear been a long day. i will try to clarify better if you need me to.

Use the INDIRECT function. INDIRECT converts a text string into a cell reference. It doesn't care if the cell that it is "referencing" doesn't exist anymore since it's not referencing an actual cell.

=INDIRECT("Sheet2!A2")

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Yeah I solved it with indirect by I wanted to be able to auto fill with it down the column. I changed the formula to =INDIRECT("Sheet2!$A$" & Row(A2))

Just FYI (and you may know this already)... Depending on how/where you are using the formula, you may not need the A2 in the ROW function.

=ROW() will return the row number of the Row that the function is used in.

e.g. =ROW() used anywhere in Row 3 will return 3.

Therefore =ROW()+1 or =ROW()-4 can be used to offset the value returned by ROW() to match your needs. Obviously, as you drag that down, the ROW() function itself will increment so the final result will increment also.

That may not make much difference in this case, but the "offset syntax" can be helpful in other situations, such as when you might want to specify a Row based on a calculation in another cell:

=ROW()+A4 where A4 contains the offset value.

or

=ROW()-VLOOKUP(A1,$B$1:$C$10,2,0) where the VLOOKUP returns the offset value based on the lookup of A1.

Just tossing that out in case it can help.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

Hey derbydad that's great advice I didn't know about the offset values. I was getting an error and that's probably why. Thanks again for the great advice.

Ask Your Question

Weekly Poll