#REF error

royhern

Board Regular
Joined
Feb 21, 2002
Messages
158
Looking for a possible answer to this question.

I have a workbook with several tabs and one of the tabs is the Data tab. I want to copy the Data tab into another workbook that needs this data but i get a #Ref error within the formula, and Vlook-up does not work. Is their a way to resolve this without copying and pasting all the data from one workbook into another?

Thanks, Roy
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
On 2002-08-29 03:30, royhern wrote:
Looking for a possible answer to this question.

I have a workbook with several tabs and one of the tabs is the Data tab. I want to copy the Data tab into another workbook that needs this data but i get a #Ref error within the formula, and Vlook-up does not work. Is their a way to resolve this without copying and pasting all the data from one workbook into another?

Thanks, Roy

What formula are you using and is the source WB open when you get the error?
 

royhern

Board Regular
Joined
Feb 21, 2002
Messages
158
I have both workbooks opened. I first delete the Data tab from the distination workbook and copied the Data tab from one into the other. The first formula is how it looks before i copied the tab and the second is how it looks after the Data tab get copied into the workbook.

=IF(ISERROR((IF(ISBLANK(VLOOKUP('Cover pg 1'!I42, DATA!1:65536,3,FALSE)),"",(VLOOKUP('Cover pg 1'!I42, DATA!1:65536,3,FALSE))))),"",(IF(ISBLANK(VLOOKUP('Cover pg 1'!I42, DATA!1:65536,3,FALSE)),"",(VLOOKUP('Cover pg 1'!I42, DATA!1:65536,3,FALSE)))))

=IF(ISERROR((IF(ISBLANK(VLOOKUP('Cover pg 1'!I42, #REF!1:65536,3,FALSE)),"",(VLOOKUP('Cover pg 1'!I42, #REF!1:65536,3,FALSE))))),"",(IF(ISBLANK(VLOOKUP('Cover pg 1'!I42, #REF!1:65536,3,FALSE)),"",(VLOOKUP('Cover pg 1'!I42, #REF!1:65536,3,FALSE)))))


Thanks for the help !! Roy
 

s-o-s

Active Member
Joined
Apr 14, 2002
Messages
384
Roy,

It looks as though you are deleting the rows(or columns) which is generating the error.

Instead you need to delete just the data (the equivalent of Clear Contents on the shortcut [right click] menu) or Select data and hit the delete button.

When you do this it will initially generate an #N/A error result to your formula. And then it should update with the correct value after you paste in the new data.

Hope this helps.
Sean. :)
 

royhern

Board Regular
Joined
Feb 21, 2002
Messages
158
The main goal is to make copy or move the data tab with all the info into another workbook that needs this data. Copying and pasting just takes too long.

any other suggestions....thanks, Roy
 

Forum statistics

Threads
1,148,049
Messages
5,744,509
Members
423,881
Latest member
Nguyen Vu

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
Top