MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Please Please Help! I need a solution ASAP!

Posted by Anna Daly on December 04, 2000 2:37 PM

Hi All,

I have a big problem with a spreadsheet that I am due to give someone today!

The problem is that I have formulas in cells that reference a worksheet. However the worksheet that they reference is imported into the workbook with some VBA code. The error that I am getting is the REF! error in the formulas that refer to the imported spreadsheet. This I know is due because Excel is trying to calculate the formulas before the sheet exsists. I cannot stop Excel from trying to do this. I have tryed turning the calculate sheet to manual and then turning it back to automatic once the sheet has been imported , but this does not seem to work. Any ideas plese. Your help will be very very greatfully received.

The kindest regards


Posted by Celia on December 04, 2000 3:37 PM

Have a look at 4291.html


Posted by Aaron McLean on December 04, 2000 3:39 PM

I don't know your exact situation but you might try this:
Change all references to the imported worksheet to a blank worksheet currently in your workbook.
After the new worksheet has been imported, run a macro or add code to your existing macro that will replace the cell references to the new worksheet.

Posted by Jack on December 04, 2000 11:51 PM

Hopefully this is not too late. I have discovered something in Excel 2000 that will automatically update links. Make sure that is turned off in your options. Even then, you may have to have the sender copy and paste the values only into that spreadsheet.