Index Formula?

Laura Bell

I am trying to make an employee sign in sheet that can automatically be updated each day. The supervisor's name is a drop box and they can single select the foreman. I want the "Name" column to update automatically the names that are associated with each foreman from the second screenshot. The sign in sheet would look like this:

I am wondering if I can get it to pull from this sheet to fill:

So, if the foreman is DJ Bubolz, then find all employees assigned to him and put on the sign in sheet.

Thanks,

Laura

Paul Newcome

DO you have the list of everyone that is assigned to each foreman already created?

Laura Bell

It could change daily. But I will put one together quickly. So, by the time you read this...yes I have a list.

Paul Newcome

Ok. You are going to need to pre-populate the sign-in sheet to account for the maximum number of names that will need to be listed at once. So if the foreman that has the most people assigned to them has 35 people, I would suggest pre-populating for 50 just in case.

The first step will be to add a text/number type column on the sign-in sheet where you manually enter 1, 2, 3, 4, 5, etc.... until you have populated the rows mentioned above. Start in the first row that you want the names listed out to start.

On the listing sheet that contains all personnel, you are going to want to insert two columns. One system generated auto-number column (no special formatting needed) and a text/number column with the following column formula:

=MATCH([Auto-Number]@row, [Auto-Number]:[Auto-Number], 0)

Next we move back to the sign-in sheet where I am going to have to make a couple of assumptions.

1. The column where the names are listed is called "Name"
2. The column where "DJ Bubolz" is housed is called "Signature"
3. The row where "DJ Bubolz" is listed is row 2 meaning "DJ Bubolz" is populated in the cell Signature2

Then in the name column where we want to start populating names, we will use this formula (dragfill down to populate all of those numbered rows)

=IFERROR(INDEX({Listing Sheet Name Column}, LARGE(COLLECT({Listing Sheet Helper Text/Number Column}, {Listing Sheet Foreman Column}, $Signature$2), "")

Laura Bell

Okay....that will take a minute to try, but I'm on it. Thank you so much.