Copying date from one sheet to another based on time left

[Garetht2014]

I am trying to auto populate the sheet 'SPEQ Due' with the information from 'Sheet 1' depending on the column for 'Time left for SPEQ'. When the 'Time left for SPEQ' reaches 1 Month, 0 days I will like the 'SPEQ Due' sheet to automatically get the data and populate the fields below? Is anyone able to help me this please ?



Sheet 1

Engineer Name​	Manager Name​	Latest SPEQ Date​	Last SPEQ Date​	Next SPEQ due date​	Time left for SPEQ​
Engineer 1​	Manager 1​	21/04/2024		21/09/2024	0 months, 1 days​
Engineer 2​	Manager 2​	21/08/2024		21/01/2025	4 months, 1 days​
Engineer 3​	Manager 3​	20/05/2024		20/10/2024	1 months, 0 days​

SPEQ Due:

Engineer Name​	Manager Name​	Latest SPEQ Date​	Next SPEQ due date​	Time left for SPEQ​
Engineer 1​	Manager 1​	21/04/2024	21/09/2024	0 months, 1 days​
Engineer 2​	Manager 2​	20/05/2024	20/10/2024	1 months, 0 days​

 

[Vogateer]

Is the Time left for SPEQ a text field? If so, did you want to find precisely those that say, "1 months, 0 days", or did you want to find any that are greater than that, so that "4 months, 1 days" is also brought over, or less than that so that it matches what you're showing in the second table?

 

[Vogateer]

I think I see what you want. Try using the following formula in A2 of your SPEQ Due sheet (or the first cell under the header row), adjusting the Sheet1!$A$2:$F$4 to match the data range you're using for Sheet1 (the Sheet1 range without including the headers).

=LET(sheet1_range, Sheet1!$A$2:$F$4, time_left_column,CHOOSECOLS(sheet1_range,6),filtered, CHOOSECOLS(FILTER(sheet1_range, (NUMBERVALUE(LEFT(time_left_column,1))<1)+((NUMBERVALUE(LEFT(time_left_column,1))=1)*(NUMBERVALUE(MID(time_left_column,11,1))=0))), 1,2,3,5,6), filtered)