External Spreadsheet Link in text format is not an "actual link".

rappleby

New Member
Joined
Sep 23, 2011
Messages
11
Using excel 2016

Cell A1 of the spreadsheet contains the current year (2020). Cell C8 contains the net sales for the month. I am subtracting the previous year's net sales from the current year (to show an increase or decrease) by using the following:

1. Create the text of the link using CONCATENATE if a value is entered in C8 of the 2020 worksheet and place the result in C46 on the 2020 worksheet:
=CONCATENATE("=IF(C8,C8-'C:\Users\Ray\Documents\Government\CRA\"&$A$1-1,"\","["&$A$1-1," Company Expenses and Summary.xlsm]"&$A$1-1," Tax Expense Summary'","!C$8,0)")
results in: =IF(C8,C8-'C:\Users\Ray\Documents\Government\CRA\2019\[2019 Company Expenses and Summary.xlsm]2019 Tax Expense Summary'!C$8,0) as text;

2. Evaluate the result using a defined function =EVAL(C46)

Function Definition in VBA Module:

Function EVAL(r As Range) As Variant
EVAL = Evaluate(r.Value)
End Function

If the 2019 Worksheet is open, the correct value is calculated but because the links are in text form as opposed to an actual formula containing the link, Excel does not see it as a link and will not calculate if the 2019 Worksheet is closed.

I'm using a macro to automatically open the 2019 worksheet when I open the 2020 worksheet, but I was wondering if there was any way that you can get Excel to recognize the text as an actual link so that the 2020 sheet is updated without having to open the 2019 sheet?
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Watch MrExcel Video

Forum statistics

Threads
1,127,326
Messages
5,624,019
Members
416,006
Latest member
PCaffrey

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top