Forum Discussion

Lumbyr's avatar
Lumbyr
Copper Contributor
Nov 25, 2018

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

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

excel
Formulas and Functions
  • Lumbyr's avatar
    Lumbyr
    Copper Contributor
    Nov 25, 2018
    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)))

Resources