Jan 31 2023 01:38 PM
Hello!
I would like to auto-populate from the individual CSS sheets into the All Jobs sheet or vice versa. For example, if I add a job, in the All Jobs sheet, can it auto populate into the individual sheets based on the Contract Services Sup. selection I make OR can the All Jobs sheet automatically fill in if I add to the individual CSS sheets? Are either of these options possible?
See attached.
Any assistance is much appreciated!
Jan 31 2023 06:13 PM
Hello @kaylaw,
See the image below,
formula on B2:
=IFERROR(IF(HLOOKUP(Table3[[#Headers],[Job]],
INDIRECT("'"&$A$2&"'!$A$1:$E$22"),
ROWS($A$1:$A2),FALSE)="","",
HLOOKUP(Table3[[#Headers],[Job]],
INDIRECT("'"&$A$2&"'!$A$1:$E$22"),
ROWS($A$1:$A2),FALSE)),"")
then drag (not copy) it to % Completed column. and then fill it downward.
sample file is on the attached.
Feb 01 2023 05:40 AM
Feb 01 2023 06:26 AM - edited Feb 01 2023 06:27 AM
A variant, if you have access to Lambda.
Stack - a 3D stack of all CSS sheets
=VSTACK('CSS1:CSS3'!$A$2:$E$1001)
Lambda 'Pad' - designed to take each 'CSS' and repeat it 1000x
=LAMBDA(a,v,VSTACK(a, EXPAND(v, 1000, , v)))
'Pad' is called to stack the CSS names
=LET(r, ROWS(Stack) / 3, DROP(REDUCE("", {"CSS1", "CSS2", "CSS3"}, Pad), 1))
Lambda 'CSS' - this function takes a stack of 3,000 rows. It only pulls rows with the selected CSS from the drop down and rows that are not blank.
=LAMBDA(Contractor_Service_Sup,FILTER(Stack, (CSS_Stack = Contractor_Service_Sup) * (TAKE(Stack, , 1) <> 0)))
Sheet level formula:
=CSS(A2)