Running formulas on worksheets after importing them from other workbooks

I have a workbook that uses VBA code to import additional sheets from another workbook. The code is shown below. The problem is that any functions that reference the imported sheet return #Ref! even after the sheet is imported. I have attached two spreadsheets to demonstrate this problem as follows.

1. Open the "Calculation Workbook" and enable the macros.
2. The function is in the blue highlighted cell and references the "Data Sheet"

Please Login or Register  to view this content.

3. Press the "Import" button which will prompt you for a file, select the "Data Workbook" This will add the "Data Sheet" to the "Calculation Workbook".
4. Go back to the "Calculation Sheet"
5. Use the drop down list in the red highlighted cell to select "TRUE" This enables the formula in the blue cell to run.
6. The function should sum the cells 'Data Sheet'!A1:A30 and return a value of 150.6919 but it returns #Ref!
7. If you select the blue cell and then click into the function box and hit enter the function will run as it is supposed to.

Can anyone explain how to get the function to run properly once the sheet is imported?

Thanks

Please Login or Register  to view this content.