VLOOKUP, IF function or any Function: To get value from left Validated cell drop down list & bring whole ROW record at right side.

[yousafnoor]

Dear Friends,

A: I have a worksheet of " Offshore Weekly Crew Change of Staff". I have all names (40 Names) in the left most column with validated (Drop down list). I need an Excel function to take any value from the left side validated drop down value and bring the corresponding ROW vales at right side. I need to bring 03 values only.

: I also need all staff can fill their names but the formula must remain intact even if some staff put manual value. Actually time to time staff is changing and manual entry is inevitable as well.

 

[oldbrewer]

[TABLE="width: 448"]
<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD]bill[/TD]
[TD]info1[/TD]
[TD]info2[/TD]
[TD]info3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]fred[/TD]
[TD]info4[/TD]
[TD]info5[/TD]
[TD]info6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]harry[/TD]
[TD]info7[/TD]
[TD]info8[/TD]
[TD]info9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]sid[/TD]
[TD]info10[/TD]
[TD]info11[/TD]
[TD]info12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]tom[/TD]
[TD]info13[/TD]
[TD]info14[/TD]
[TD]info15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]harry[/TD]
[TD]info7[/TD]
[TD]info8[/TD]
[TD]info9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]rather than a drop down just type a name into C10[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]and use this formula[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]=OFFSET($A$1,MATCH($C10,$A$2:$A$6,0),1)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[yousafnoor]

Dear Oldbrewer

Thanks for the rely.
Actually there has to be a drop down list as I have only 12 rows to fill data as there are only 12 helicopter seat available for crew change.
On each week-day, I need to take only 12 no of staff from drop down (50 no of employees in each drop down list)I cannot make 50 rows, need only 12 rows and corresponding data of each staff who will embark/disembark to & from the copper.
I wish I could attach my excel sheet for further elaboration but no provision available.

 

[oldbrewer]

use google sheets
or
copy first 10 rows and paste into reply box

anyway, so do you have 12 identical drop down lists and choose a different name from each

 

[oldbrewer]

[TABLE="width: 960"]
<colgroup><col width="64" span="15" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]name27[/TD]
[TD="width: 64, align: right"]312[/TD]
[TD="width: 64, align: right"]33[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]name1[/TD]
[TD]idnum[/TD]
[TD]age[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]name2[/TD]
[TD="align: right"]137[/TD]
[TD="align: right"]33[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]name3[/TD]
[TD="align: right"]144[/TD]
[TD="align: right"]37[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]name4[/TD]
[TD="align: right"]151[/TD]
[TD="align: right"]39[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]name5[/TD]
[TD="align: right"]158[/TD]
[TD="align: right"]36[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]name6[/TD]
[TD="align: right"]165[/TD]
[TD="align: right"]41[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]name7[/TD]
[TD="align: right"]172[/TD]
[TD="align: right"]33[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]name8[/TD]
[TD="align: right"]179[/TD]
[TD="align: right"]37[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]name9[/TD]
[TD="align: right"]186[/TD]
[TD="align: right"]39[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]name10[/TD]
[TD="align: right"]193[/TD]
[TD="align: right"]36[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]name11[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]41[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]name12[/TD]
[TD="align: right"]207[/TD]
[TD="align: right"]33[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]name13[/TD]
[TD="align: right"]214[/TD]
[TD="align: right"]37[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]name14[/TD]
[TD="align: right"]221[/TD]
[TD="align: right"]39[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 5"]the dropdown list is the list of names in col k[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]name15[/TD]
[TD="align: right"]228[/TD]
[TD="align: right"]36[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 4"]I selected name27 and the idnum and age[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]name16[/TD]
[TD="align: right"]235[/TD]
[TD="align: right"]41[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 5"]are obtained by a simole offset match formula[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]name17[/TD]
[TD="align: right"]242[/TD]
[TD="align: right"]33[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]name18[/TD]
[TD="align: right"]249[/TD]
[TD="align: right"]37[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]name19[/TD]
[TD="align: right"]256[/TD]
[TD="align: right"]39[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]name20[/TD]
[TD="align: right"]263[/TD]
[TD="align: right"]36[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]name21[/TD]
[TD="align: right"]270[/TD]
[TD="align: right"]41[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]name22[/TD]
[TD="align: right"]277[/TD]
[TD="align: right"]33[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]name23[/TD]
[TD="align: right"]284[/TD]
[TD="align: right"]37[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]name24[/TD]
[TD="align: right"]291[/TD]
[TD="align: right"]39[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]name25[/TD]
[TD="align: right"]298[/TD]
[TD="align: right"]36[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]name26[/TD]
[TD="align: right"]305[/TD]
[TD="align: right"]41[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]name27[/TD]
[TD="align: right"]312[/TD]
[TD="align: right"]33[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]name28[/TD]
[TD="align: right"]319[/TD]
[TD="align: right"]37[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]name29[/TD]
[TD="align: right"]326[/TD]
[TD="align: right"]39[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]name30[/TD]
[TD="align: right"]333[/TD]
[TD="align: right"]36[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[yousafnoor]

Dear Olbrewer

Many thanks for help. Actually I tried to use VLOOKUP with IFFERROR function to remove "#N/A" as 12 seats need to make available but employees are not always 12 Some time only 02 staff to be booked for helicopter weekly crew change.
The functions worked perfect but a problem still exists:
If less than12 passengers to be booked, the remaining cells contents to be deleted and then formula goes !

Can you please help on:

The formula must not be deleted but "clear contents" or "delete" action becomes possible. If formula is deleted then not all employees who book copter seat each week are Excel literate.

I used :

+IFERROR(VLOOP($D$2,$D$64,staff_list,column(B1),false),""), while ColumnB1 is nested inside VLOOKUP.
Please help all is working great only , if staff number is less than 12 then contents deletion deletes the formula as well?

 

[oldbrewer]

Is the helicopter booking "first come, first served" ? If so, the first 12 people to reserve a seat get to fly. Same goes for return trip....

 

[yousafnoor]

Dear Oldbrewer,

Once again thanks from the realms of my heart upon your time to help me. No sir, booking is in advance. We send the Excel sheet, which is in discussion, to the camp boss and He sends to the scheduler for actual manifest to be made.
If I protect formula, I cannot drag to fill the lower "deleted" rows, on less number of passengers. Any inadvertent booking costs our company 1000s of $.
Also staff is changing on transfer to other station, sacked, sick or on leave. Is it possible to make a macro button and fill the "formula-deleted" row with just one click?. The booking staff does not even know what is an absolute reference in Excel sheet?
They might call me middle of the night for problems. I have made a validation list of 62 people and relocated "named range list" to some other location and I can also hide the cells and keep only 12 rows for each week day.
The good thing is that VLOOKUP can communicate to the hidden cells a well.

 

[oldbrewer]

My problem is I cannot understand your issue. Individual members of staff "tell you when they need to fly out and back". So you send that list, containing say 2 to 12 names to the scheduler, and he books the flights. You could be informed of the need to fly in an Email. Keep it simple, that is my advice.

 

[yousafnoor]

Can I get your mail to send the file?