2 workbooks - trying to get formula to update workbook 1 from workbook 2 based on today's date.

Highlighted
New Contributor

Hi All. 

 

Really stuck.

 

As stated I have 2 separate workbooks. Workbook 1 has a table with 365 days listed in column A. In column B I need a formula that picks up data entered into Row 6 of workbook 2 automatically (this is updated daily). 

The closest i can get is as follows:

=IF(A:A=TODAY(),INDEX('[NDG 2018 Calendar.xlsm]Calendar'!$6:$6,MATCH(9.99999999999999E+307,'[NDG 2018 Calendar.xlsm]Calendar'!$6:$6)))

 

However whilst it updates todays's cell it returns the previous days cell as false, instead of keeping the previous days entry.

 

I have attached both workbooks and taken out any non relevant information. Please see row 331 onwards for formula in workbook 1.

In Workbook 2 row 6 his highlighted in red.

 

Regards Richard

1 Reply
Highlighted
Can any one explain why when i put today(-1) when false it does not work?

=IF(A:A=TODAY(),(0),Today(-1),INDEX('[NDG 2018 Calendar.xlsm]Calendar'!$6:$6,MATCH(9.99999999999999E+307,'[NDG 2018 Calendar.xlsm]Calendar'!$6:$6)))