Auto populate data from another sheet

Copper Contributor

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!

kaylaw_0-1675201080039.pngkaylaw_1-1675201103232.png

 

3 Replies

Hello @kaylaw,

See the image below,

Capture.JPG

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.

@Rr_ 

 

Thank you so much for taking the time to help with this!!

@kaylaw 

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)

 

Patrick2788_0-1675261590455.png