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]

bill	info1	info2	info3
fred	info4	info5	info6
harry	info7	info8	info9
sid	info10	info11	info12
tom	info13	info14	info15
		harry	info7	info8	info9
		rather than a drop down just type a name into C10
		and use this formula
		=OFFSET($A$1,MATCH($C10,$A$2:$A$6,0),1)

<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[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]

name27	312	33
										name1	idnum	age
										name2	137	33
										name3	144	37
										name4	151	39
										name5	158	36
										name6	165	41
										name7	172	33
										name8	179	37
										name9	186	39
										name10	193	36
										name11	200	41
										name12	207	33
										name13	214	37
										name14	221	39
	the dropdown list is the list of names in col k					name15	228	36
	I selected name27 and the idnum and age						name16	235	41
	are obtained by a simole offset match formula					name17	242	33
										name18	249	37
										name19	256	39
										name20	263	36
										name21	270	41
										name22	277	33
										name23	284	37
										name24	291	39
										name25	298	36
										name26	305	41
										name27	312	33
										name28	319	37
										name29	326	39
										name30	333	36

<colgroup><col width="64" span="15" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[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?