Jan 18 2023 03:23 AM
Hello everybody,
I would like to know if it is possible to ensure that when you enter data in one sheet, they are automatically inserted in another sheet.
Eg:
In the "General" sheet I have all the reservations.
The other sheets are the names of those who made the reservations.
I want that when I insert a new sheet with a new person and insert data there, that same data insert automatically in the "General" sheet.
Thank you!
PS. Attached is the excel file in question.
Jan 18 2023 08:06 AM
This solution will work if you have access to Lambda. I've created a function to roll-up the sheets.
3-D reference 'AllSheets'
='Mr. Esposito:Mr. Cristella'!$A$3:$D$302
'Applicants' constant
={"MR. ESPOSITO"; "MR. BALESTRA"; "MR. CRISTELLA"}
StackApplicants - this creates an array of applicants. Each applicant is repeated 300 times to match the 300 records (potentially) pulled for each sheet.
=DROP(REDUCE("", Applicants, LAMBDA(a, v, VSTACK(a, EXPAND(v, 300, , v)))), 1)
'RollUp' Lambda. Consolidate all sheets. Filter out sheets where the requested date is empty.
=LAMBDA(sheets,LET(
stack, VSTACK(sheets),
records, HSTACK(TAKE(stack, , 3), StackApplicants, TAKE(stack, , -1)),
FILTER(records, TAKE(records, , -1) <> 0)
))
Sheet level formula:
=RollUp(AllSheets)
Jan 18 2023 09:11 AM
Jan 18 2023 09:47 AM