Feb 13 2023 12:24 AM - edited Feb 13 2023 12:38 AM
Hi, All
We are using formulas to pull data from other sources to populate an 'as built' record of works completed on site. However, the surveyors working for the client keep predicting works for the future. Friday's sheet update shows works completed today (Monday), for example... and as with much in life, nothing is certain. The problem comes with our reporting of completed works as there are multiple graphs and durations etc. based on dates, so what I would like to do is insert a condition to the below function that will only populate the cell if the date (taken from a cell on the 'McLH As built - Pile logs' sheet - Column H) is yesterday or older... Basically not today or a future date.
As ever, your help is greatly appreciated.
NW
=IF(XLOOKUP([@Name],'McLH As built - Pile logs'!A:A,'McLH As built - Pile logs'!I:I)=0,"",XLOOKUP([@Name],'McLH As built - Pile logs'!A:A,'McLH As built - Pile logs'!I:I))
Feb 13 2023 01:32 AM - edited Feb 13 2023 01:33 AM
SolutionPerhaps
=IF(OR(XLOOKUP([@Name],'McLH As built - Pile logs'!A:A,'McLH As built - Pile logs'!I:I)=0,XLOOKUP([@Name],'McLH As built - Pile logs'!A:A,'McLH As built - Pile logs'!I:I)>=TODAY()),"",XLOOKUP([@Name],'McLH As built - Pile logs'!A:A,'McLH As built - Pile logs'!I:I))
or if you have Microsoft 365 or Office 2021:
=LET(d, XLOOKUP([@Name],'McLH As built - Pile logs'!A:A,'McLH As built - Pile logs'!I:I), IF(OR(d=0, d>=TODAY()), "", d)
Feb 13 2023 02:06 AM
Feb 13 2023 01:32 AM - edited Feb 13 2023 01:33 AM
SolutionPerhaps
=IF(OR(XLOOKUP([@Name],'McLH As built - Pile logs'!A:A,'McLH As built - Pile logs'!I:I)=0,XLOOKUP([@Name],'McLH As built - Pile logs'!A:A,'McLH As built - Pile logs'!I:I)>=TODAY()),"",XLOOKUP([@Name],'McLH As built - Pile logs'!A:A,'McLH As built - Pile logs'!I:I))
or if you have Microsoft 365 or Office 2021:
=LET(d, XLOOKUP([@Name],'McLH As built - Pile logs'!A:A,'McLH As built - Pile logs'!I:I), IF(OR(d=0, d>=TODAY()), "", d)