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?
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?
