Autopopulate formula across worksheets?

Question

&nbsp;I am trying to create a lunch schedule for the year with approx 30 people. Each day a new person will be chosen (with their initials on Picture 1), and then it will automatically rotate (sheet 1). Sheet 2 will have their name, the date of their scheduled lunch, and what they are bringing. How do I autopopulate the date from sheet 1 to sheet 2? Also it's not exactly perfect, because people get sick or are away, and then I have to substitute someone else who is coming that day to do lunch, and I want the date to autopopulate as well on Sheet 2 when I have to make a change on Sheet 1. Hope what I'm asking for makes sense. How do I do this?&nbsp;

sheethappensxl · Answer

Sounds like you're rotating lunch duties and want the scheduled date to automatically appear on each person's sheet, even if you swap someone last minute.

One approach:

On Sheet 2, use a formula like INDEX + MATCH or FILTER to pull the date based on where someone’s initials show up on Sheet 1.
If initials don’t match full names exactly, set up a helper range to link them.
If someone’s out and you manually change Sheet 1, the date on Sheet 2 will auto-update too — as long as the formulas are tied to what’s on Sheet 1.

peterbartholomew1 · Answer

For each output table, first look up the appropriate row of the schedule by name,&nbsp; Then selected dates corresponding to non-blanks.&nbsp; Finally transpose results to column.= LET(
    personSchedule, XLOOKUP(person, names, schedule),
    scheduleDates,  IF(ISTEXT(personSchedule), dates, NA()),
    TOCOL(scheduleDates, 3)
  )&nbsp;

m_tarler · Answer

alternatively:=TRANSPOSE(FILTER( _DATES_, CHOOSEROWS( _SCHEDULE_, XMATCH( _NAME_, _NAMES_)&lt;&gt;"", "")where _DATES_, _SCHEDULE_, and _NAMES_ are the corresponding ranges on sheet1 and _NAME_ &nbsp;is the name to look up&nbsp;&nbsp;

Forum Discussion

Autopopulate formula across worksheets?

3 Replies