Linking Entire Sheet to another Worksheet

[ToddListon]

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.

[#2]

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?

[#3]

@JKPieterse 

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. 

[#4]

@ToddListon 

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.

[#5]

Separate the logic I would say: presentation is a different process than getting in the data. So get the data in using a query (into a table or directly feeding a pivottable or whatever you need), then use other techniques within the file to create a nice presentation sheet.

[#6]

Ah, thank you for asking for the clarification. Originally I tried all configurations, both open, both closed, one open/one closed. And none work. I tried again just now, just in case. When closed I get the full file extension built into the formula, and double checked to make sure that was correct as well.

[#7]

Okay, thank you for the recommendation.

[#8]

@ToddListon 

it should work when both files are opened or closed, here's a sample screen shot of one that works:

look at the formula bar

[#9]

Ah, yes. I see what you are sharing. And I see how the formula works there. Thank you.

The difference with the formula you shared and what I shared is the specific cell references. You have here an external reference to a workbook->worksheet->specific cells. I am trying to just reference Workbook->worksheet without the cells. I want the entire worksheet. And like I mentioned above, I got it to work with one workbook and worksheet, but not the other. I don't know why only one is working.

I can get it to work when I reference specific cells. It works every time. As soon as I try to only reference the worksheet in the US CF 21 workbook, the formula no longer works.

[#10]

@ToddListon 

So the question for you to ask and logically thinking about it is, how would excel know which specific value you want returned if you're only referencing the entire workbook/worksheet/cell address (column or row or both)?

Excel is a triangulation equation calculation tool. Let's talk about this concept in a form of analogy.

Think of the geography of New York city as a worksheet, Avenues are columns and Streets are rows.

If I gave you a map of New York City and instruct you to deliver a package to someone in New York (you are excel engine in this example and New York is the worksheet in this example) so I gave you a package (value) and gave you a map of the worksheet without telling you the specific cell address of where to drop that value (package) off how are you going to know where to drop the package in New York City?

Its the same with excel.

[#11]

I can get behind this analogy, and I see the logic. While I get that excel may need the triangulation like you mentioned, what I don't understand is again, why did it work for me once but not twice to only reference the workbook/worksheet without any columns or rows.

In my original question I said that I only referenced 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, and it worked.
='TW Cash forecast Mar 26''21.xlsx'!USD

But then when I then tried the below similar formula referencing a different workbook and worksheet I get the #NAME? error.
Here's the link formula to a different workbook and worksheet that is not working:
='CF US 21.xlsx'!CashFlowForecast

So I was able to get one formula to work without the specific cell address but not the other. I don't understand why excel would allow one, but not the other. And like I said, I tried both with both workbooks open and closed to no success.

If I had failed on getting either to work I would assume that I excel required a specific cell address for external referencing to work. But since one of two works, and continues to work regardless of me erasing and rewriting the formula or opening and closing the workbook, I am led to believe that excel allows this type of external referencing, I just have something else wrong.

[#12 · Accepted solution]

@ToddListon 

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.

[#13]

I missed checking that... A huge chunk of the sheet was defined with a name. So that was the answer. Thank you for sticking with me on this.

[#14]

@ToddListon 

excel already told you where to look, you just weren't listening to it. lol

[#15]

Yeah, I'm just new enough to the errors to not understand what should have been clear. lol

[#16]

@ToddListon 

Thank you for marking the answer, other users with the same scenario questions will now be able to search the community to find their answer.

Have a great week!

cheers

[#17]

@ToddListon I referred to a missing defined name in my first reply in the thread, perhaps I wasn't too clear about what I meant 🙂