#REF! Error help.

[kapadons]

=IF(Sheet3!C3="","0",VLOOKUP(Sheet3!C3,List4,2,0)) is my formula.


My issue is that if I delete the row that C3 is in on sheet3 or paste new data from another cell , the forumla turns out a #REF! error

=IF(Sheet3!#REF!="","",VLOOKUP(Sheet3!#REF!,List4,2,0))

By deleting the row on the main sheet or pasting in different data from another cell ,I would like my formula to keep referencing the same cells , just use the data that got shifted up. To clarify, keep looking at cell C3 on sheet3, just use the new text that in in the box for my vlookup formula.


Thank you in advance for any help you can offer.

 

[Jonmo1]

You can change

Sheet3!C3
to
INDEX(Sheet3!C:C,3)

Hope that helps.

 

[kapadons]

You can change

Sheet3!C3
to
INDEX(Sheet3!C:C,3)

Hope that helps.

Perhaps I am missing something, That goves me a "You have entered to many arguments" error.

 

[T. Valko]

=IF(Sheet3!C3="","0",VLOOKUP(Sheet3!C3,List4,2,0)) is my formula.


My issue is that if I delete the row that C3 is in on sheet3 or paste new data from another cell , the forumla turns out a #REF! error

=IF(Sheet3!#REF!="","",VLOOKUP(Sheet3!#REF!,List4,2,0))

By deleting the row on the main sheet or pasting in different data from another cell ,I would like my formula to keep referencing the same cells , just use the data that got shifted up. To clarify, keep looking at cell C3 on sheet3, just use the new text that in in the box for my vlookup formula.


Thank you in advance for any help you can offer.

Try this...

=IF(INDIRECT("Sheet3!C3")="",0,VLOOKUP(INDIRECT("Sheet3!C3"),List4,2,0))

 

[kapadons]

Try this...

=IF(INDIRECT("Sheet3!C3")="",0,VLOOKUP(INDIRECT("Sheet3!C3"),List4,2,0))

Once again, you never fail to amaze me. worked perfectly.

 

[kapadons]

No way to auto fill with this equation?

 

[T. Valko]

No way to auto fill with this equation?

Which way do you want to fill?

Like this:

Sheet3!C3
Sheet3!C4
Sheet3!C5
Sheet3!C6
etc
etc

If so, try this...

=IF(INDIRECT("Sheet3!C"&ROWS(A$1:A3))="",0,VLOOKUP(INDIRECT("Sheet3!C"&ROWS(A$1:A3)),List4,2,0))

 

[kapadons]

Thanks again. Worked just fine.

 

[T. Valko]

Thanks again. Worked just fine.

You're welcome. Thanks for the feedback!