drop down list to automatically fill cells to right.

Hi,

I am trying to make a spread sheet for peoples skills and use a data validation list, but I need it to automatically fill the cells to the right after selecting someones name from the list. To confuse things even more to select two persons on one line and the automatically put the highest skill level in the cells to the right.

For example. I have a data sheet, this will be the persons name in A and then set skills to the next five rows B, C, D, E and F.


On a separate sheet I want it to select the name from a drop down list from the data sheet and then to carry forward the B, C, D, E and F cell data.

If possible then to have two names per row (people working together) and then the B, C, D, E and F cells to show the highest qualification out of the two names selected.

Hope this is clear and any help would be really appreciated.

Thanks.

Adam

You would normally use VLOOKUP for this, for example, with your data validation in cell A2 of Sheet2, put this in B2:

=IFERROR(VLOOKUP($A2,Sheet1!$A:$E,columns($A:B),0),"")

then copy across into C2:F2.

I don't know what you mean by your second request - please attach a sample workbook to explain further (the FAQ describes how to attach a file to a post).

Hope this helps.

Pete

Thanks for the reply, I have made a quick spreadsheet to show what I mean and attached it.

So what I need, all information will go on the data sheet, staffs name followed by skill levels etc.

Then on the rosta sheet have a drop down list to select the persons name and then the skill boxes to automatically update with that on the data sheet.

But the skill levels will show the highest skill on that group.

Ie. group one to be Matt and Claire so the security level and training level will both show 1 as Matt has the highest clearance.

Hope this helps a bit more and again thanks for any replies.

Adam

Put this formula in cell D4:

=IF(OR($B4="",$C4=""),"",MIN(VLOOKUP($B4,Data!$B$4:$D$7,COLUMNS($A:B),0),VLOOKUP($C4,Data!$B$4:$D$7,COLUMNS($A:B),0)))

then copy it into E4, then copy D4:E4 down as far as you need to. The formula will show a blank entry until both names have been selected.

Hope this helps.

Pete

now that's getting to a be a sum and a half,

I should of asked before but I thought with one answer I would be able to work out others but is too complicated for me, been too long since I last used excel and I am realising all but basic knowledge has gone.

Is there any way to adapt it to show only one persons stats if they are working on their own?

Also for different data answers for example. (high, med, low) or (yes,no) rather then the numbers?

Thanks again for all your help Pete.

As in the attached file, you can put this amended formula in D4:

=IF(AND($B4="",$C4=""),"",IF($C4="",VLOOKUP($B4,Data!$B$4:$D$7,COLUMNS($A:B),0),MIN(VLOOKUP($B4,Data!$B$4:$D$7,COLUMNS($A:B),0),VLOOKUP($C4,Data!$B$4:$D$7,COLUMNS($A:B),0))))

and then copy it into E4, and copy those two cells down.

Now if both names are missing you will get a blank, but if the name is missing from column C then you will get the details for the person in column B, but if both names are present then it will give the highest clearance of the two. If you want to return words instead of numbers, it can be done like this in D4:

=IF(AND($B4="",$C4=""),"",IF($C4="",CHOOSE(VLOOKUP($B4,Data!$B$4:$D$7,COLUMNS($A:B),0),"High","Medium","Low","Very Low"),CHOOSE(MIN(VLOOKUP($B4,Data!$B$4:$D$7,COLUMNS($A:B),0),VLOOKUP($C4,Data!$B$4:$D$7,COLUMNS($A:B),0)),"High","Medium","Low","Very Low")))

and then you don't need to change your table. MIN only works on numbers anyway.

Hope this helps.

Pete

Thank you Pete for all your time and effort

Ok, I thought I had it.... but no....

This time I have actually thought about the spread sheet I want to create and attached it below. column E works perfectly, Pete if your still up for lending a hand please can you show me how to do the other columns with a yes/no answer from the data sheet (so example for skill 1 if two people are together with a yes and a no for that skill, the team will show as yes... the team collectively possess that skill)

Thanks again for your help.

perfect!, thanks very much