Oct 12 2021 03:51 PM
Oct 12 2021 03:51 PM
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.
Oct 14 2021 05:55 AM
Oct 14 2021 04:36 PM
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.
Oct 14 2021 09:59 PM
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.
Oct 15 2021 02:40 AM
Oct 15 2021 08:53 AM
Oct 15 2021 12:37 PM
Oct 15 2021 12:50 PM
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.
Oct 15 2021 01:03 PM
Oct 15 2021 03:04 PM