I have a formula that updates a date. It only updates the day and month. Why I have to open the source file to update the year?
I'm using this formula ( ='C:\Users\littl\Pictures\My System\1. January 2019\[2.xlsm]Rep 21'!$S$4:$T$4+1).
Thanks
Year not updating
-
- 3StarLounger
- Posts: 256
- Joined: 15 Aug 2016, 11:23
Re: Year not updating
Bomba,
How can someone help you, without knowing what's in your file?
Specifically, you say that you use that formula that reads in file 2.xlsm, sheet Rep 21, range S4: T4 + 1. What's in that range?
Post the file (the one in which is your formula, and the 2.xlsm file with the Rep 21 sheet) .
How can someone help you, without knowing what's in your file?
Specifically, you say that you use that formula that reads in file 2.xlsm, sheet Rep 21, range S4: T4 + 1. What's in that range?
Post the file (the one in which is your formula, and the 2.xlsm file with the Rep 21 sheet) .
-
- 3StarLounger
- Posts: 281
- Joined: 20 Jan 2019, 19:43
Re: Year not updating
The formula is in the file named 3, sheet Rep21, cell S4.
You have the link below of the folder with the files.
https://www.dropbox.com/sh/bas8040clprc ... RQI1a?dl=0
You have the link below of the folder with the files.
https://www.dropbox.com/sh/bas8040clprc ... RQI1a?dl=0
-
- Administrator
- Posts: 78542
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Year not updating
I guess it's because of the chain of links: Rep21!S4 in 3.xlsm depends on Rep21!S4 in 2.xlsm, and that in its turn depends on Rep21!S4 in 1.xlsm.
When you open 3.xlsm, it looks at 2.xlsm, but if that isn't open, the value of Rep21!S4 in that workbook is not updated...
As a workaround, you could change the formula in 3.xlsm to
='C:\Users\javog\Documents\Excel\[1.xlsm]Rep 21'!$S$4+2
instead of
='C:\Users\javog\Documents\Excel\[2.xlsm]Rep 21'!$S$4+1
When you open 3.xlsm, it looks at 2.xlsm, but if that isn't open, the value of Rep21!S4 in that workbook is not updated...
As a workaround, you could change the formula in 3.xlsm to
='C:\Users\javog\Documents\Excel\[1.xlsm]Rep 21'!$S$4+2
instead of
='C:\Users\javog\Documents\Excel\[2.xlsm]Rep 21'!$S$4+1
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 281
- Joined: 20 Jan 2019, 19:43
Re: Year not updating
Thanks a lot Master