#REF error

[royhern]

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

 

[Aladin Akyurek]

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]

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]

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]

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