2nd sheet linked to main sheet - #REF! issues upon refreshing - Any solutions? :)

[Elijah]

Hi all,

I have a rather large Excel file with multiple sheets (tables) that I'm reluctant to share due to privacy issues, but I hope my explanations will be sufficient. Alternatively, I could make a dummy sheet.

The Main Sheet consists of data in a table that is loaded (and refreshed daily or more frequent) from an SQL database using VBA. As far as I know, this sheet doesn't contain any actual formulas. It only loads data from a DB.

I have created an additional Linked Sheet that links selected parts (columns) of the Main Sheet for more efficient analysis. Additionally, I also perform some extra calculations in new columns. The way I construct this link is to simply go to the top cell for each column on Linked Sheet, enter = and then select the corresponding column/cell in the Main Sheet.

Initially, this seemed to work very well when I refreshed the current data file in Main Sheet, but when loading a new data file in the Main Sheet, all my references in the Linked Sheet were gone and simply returned #REF!.

Is there a smart solution to work around this?

There are NO changes in the Main Sheet from columns (rows) A-Z when loading new data, but it can have additional columns to the right of column Z depending on the size of the data set. But I wouldn't think this to be a factor and I don't use these additional columns either.


My current solution:

1. Create a new sheet with table that I call raw data. This sheet essentially looks the same as Main Sheet, but does not update automatically.

2. Link my Linked Sheet to the sheet above.

3. Copy and paste data from Main Sheet whenever I update into the sheet in point 1.

This works very well, BUT, after creating it, I find that it is a bit cumbersome in the long run and is an additonal step (and sheet) that I'd love to avoid.

Any ideas?

Maybe the way I set up the link is a problem, but it's the only way I know of.

Best regards,

Elijah

[Elijah]

To visualize current and ideal set-up:

Uten navn.png

[Elijah]

I also got a tip it could be resolved with settings in the Trust Center, but I've tried various settings (maybe not the right ones?) without luck at this point.

[AliGW]

What settings did you try?

[Elijah]

Enable all data connections (not recommened) under Security Settings for Data Connections.

Enable automatic update for all workbook links (not recommended) under Security Settings For Workbook Links.

[Elijah]

1. and 2. shows how it looks initially. At 3. I have loaded a new series and the result is the #REF!

Uten navn.png

[Elijah]

Any ideas?

Might there be other ways to link the data to circumvent this? Or is this unavoidable?

My current copy and paste solution DOES work, but it is a bit cumbersome and is an extra step that would be nice to avoid.

Thanks in advance for any pointers.

[JeteMc]

Since a few days have passed I would suggest doing what you state in the first paragraph of your first post, which is to upload a dummy (sample) file. I would suggest uploading two files. The first file should contain the Main and Linked sheets (1 and 2) from post #6. The second should have a sample of the Main sheet with new data loaded in the manner that causes the reference errors in the Linked sheet.
Let us know if you have any questions.

[Elijah]

Hi, JeteMc,

I appreciate the invitation. It will take me some time to recreate that, but I might do so in a while. Thinking on it, I'm not even sure it would be helpful. Since you would all just get the #REF error in the cells anyway?