#REF! Error help.

kapadons

Board Regular
Joined
Jun 16, 2012
Messages
70
=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.
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
=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))
 
Upvote 0
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))
 
Upvote 0

Forum statistics

Threads
1,211,867
Messages
6,104,460
Members
447,910
Latest member
abrook1308

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top