Auto-Fill names based on a Validation?

Spreadsheet attached;

If a Managers name is selected in F3 it will then auto populate,

D5, F5, H5 etc with the consultants within the Managers team based on information from Setup sheet.

Is this possible with formulae, or is it macro time?

Thanks,

No, I don't think it is macro time

Try this:

In I2 of Setup sheet, enter: =IF(H2='Team Overview (Breakdown)'!$F$3,COUNT($I$1:I1),"")

and copy down to bottom

then in say, G3 of the Team sheet enter: =MAX(Setup!I:I) (you can match font to background colour to hide it)

Then in D5 enter:

=IF(COUNTA($C$5:C5)+1>$G$3,"",INDEX(Setup_Consultants,MATCH(COUNTA($C$5:C5)+1,Setup!$I$2:$I$53,0)))

copy that cell and then paste to each of the other cells in that row to populate.

Thanks for catching that Dulanic,

I just missed a +1...

so formula should be:

=IF(H2='Team Overview (Breakdown)'!$F$3,COUNT($I$1:I1)+1,"")


Note: COUNT() is I think more efficient then COUNTIF()

The formula in column I checks if the manager name in H is the one you selected in the TEAM sheet. If so, it adds 1 to the count of matches as you go down the list.

The Max() formula, gives the last (largest) number returned which corresponds to the number of matches...

This is used in the final formula to "error trap". I.e. if the number of boxes is more than the max, then return a blank instead of giving an error.

For each box, a count is made of previously filled boxes, to determine what "box number" we are at... if we have a match of the corresponding "box" number in the Setup sheet, then the corresponding name (SetupConsultants column) to that number (from column I)

You can use the formula Auditing tool, found in the Tools menu to step through the formulas.