# #REF! Error help.

##### Board Regular
=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.

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
You can change

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

Hope that helps.

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.

=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.

Try this...

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

Try this...

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

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

No way to auto fill with this equation?

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))

Thanks again. Worked just fine.

Thanks again. Worked just fine.
You're welcome. Thanks for the feedback!

Replies
19
Views
789
Replies
1
Views
321
Replies
7
Views
268
Replies
5
Views
341
Replies
2
Views
230

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.

### Which adblocker are you using?

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

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