Forum Discussion
Linking Entire Sheet to another Worksheet
Hello,
I am trying to externally link an entire spreadsheet to another spreadsheet. Example: I want all of the data on Sheet1 to be on Sheet2. I also want Sheet2 to auto-update when Sheet1 does. However, I do want this externally linked, so it will be from a sheet in one workbook to a sheet in another. I don't want to have to manually update the information by selecting the specific cells which is what every website I checked so far has you do. You'll see how I bypassed that originally in the below formula. I also tried a dynamic link with a query but I don't want the data as a table since that doesn't fit the aesthetic.
I succeeded with this on one tab in my Global Forecast Workbook linking it to the TW Cash Forecast Mar 26 21 Workbook with the USD Sheet using the following formula in the A1 cell of the tab I want the information to appear on:
='TW Cash forecast Mar 26''21.xlsx'!USD
I then tried the below similar formula on another tab in the Global Forecast Workbook and get the #NAME? error. I cannot figure out why this happening. Does anyone know?
Here's the link formula to a different workbook and worksheet that is not working:
='CF US 21.xlsx'!CashFlowForecast
I appreciate any help.
You stated: I get the #NAME? error.
check this place:
check the name manager, maybe the range for the formula that worked is defined there, while the other is not.
- The formula depends on the presence of a range name in the workbook you are linking to.
If I may ask: What is wrong with having it in a table? Tables have huge advantages. Do you know you can change the formatting of the table by choosing a different table style?Thank you for responding.
Yes, I know it depends on a range name. Do you know the US CF 21 range name would not have worked when the other did work? All of the characters are correct in the stated names. There is nothing missing or added.
I don't want to use a table for two reasons. First, the information is not simply a data set, it is the final presentation of data that is being pulled from data sets. Second, the document is for aesthetics and not manipulation. It will be used by the Executives of the company to view for information only. I have played with different table styles and they don't do the job I want. If I have to turn to a query, I may, it just isn't doing what I want it to. Unlike the successful formula I mentioned above.
something is missing in your narrative, you forgot to mention if both workbooks were open or closed or if TW Cash forecast Mar 26''21.xlsx was open or closed and CF US 21.xlsx' was open or closed. Test again this time both files closed and see if both formula works.
