Forum Discussion

kaylaw's avatar
kaylaw
Copper Contributor
Jan 31, 2023

Auto populate data from another sheet

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!

 

admin
excel
Formulas and Functions

3 Replies

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Feb 01, 2023

    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)

     

     

  • Rodrigo_'s avatar
    Rodrigo_
    Iron Contributor
    Feb 01, 2023

    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.

Resources