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]

No - that is not allowed under forum rules but google sheets is ok

 

[yousafnoor]

Please tell me how to attach the google sheet here?

 

[oldbrewer]

you make a google sheet "in the cloud" and make it open access then anybody on here can look at it

 

[yousafnoor]

Thanks

I a now on the corporate PC and cannot access any cloud storage but in town in my off-week, I will post google sheet.
Talking of keeping things simple, the whole point is keeping the things simple and saving man-hours.

 

[oldbrewer]

We will get there - before I retired I was a business improvement leader and often instead of modifying a cumbersome system we redesigned it. Could you Email a copy of the sheet to your private email and go from there. If I needed a flight next Monday I would enter my name on the Monday list and the reservation would be made for me - how is it more complicated ?

 

[yousafnoor]

Completely failed to put up in a way where you could understand my problem: My fault as a lay man.
Now after beating my un-educated brain cells, it boiled down to:

"Is there a way to put a manual entry in Excel dropdown list in empty cells, while the "permanent drop down list" must not change & also take the manual entry ". I can deal with, then, other Excel functions to bring data relevant to the dropdown recalled entry.

In this way, any new visitor may be booked with all his attributes like his name, company, blah blah....

The moral of story is, some time no enlisted staff available to book, only copter-ful new visitors are booked to visit the station

 

[oldbrewer]

if A1 is the drop down, in B1 put =A1 then when drop down is selected B1 will be that selection. Then B1 can be used to fetch that individual's details. If the copter has 10 guests then only 2 additional seats available. Maybe a macro to book a seat then the macro can control the seat issue as you could put 10 in a deeply protected cell so the macro can only assign two seats ????

 

[yousafnoor]

No hope I believe!

May be I will have to protect just 1st row of validated cell and hide it to secure the formulae in the next cells of row. If thy delete all entries in a row, I can give password to their senior to unhide the row and copy/paste formula again. and then re-password it. I am sure these dudes (I am one of them of course), will call me 24/7.

 

[oldbrewer]

I will make up a quick and dirty macro model - give me an hour........

 

[oldbrewer]

[TABLE="width: 1184"]
<colgroup><col><col span="2"><col span="2"><col span="2"><col span="2"><col><col><col><col span="5"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]col C[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]12[/TD]
[TD]12[/TD]
[TD]9[/TD]
[TD]6[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]

<tbody>
</tbody>

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]seats avail[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01/01/2018[/TD]
[TD="align: right"]02/01/2018[/TD]
[TD="align: right"]03/01/2018[/TD]
[TD="align: right"]04/01/2018[/TD]
[TD="align: right"]05/01/2018[/TD]
[TD="align: right"]06/01/2018[/TD]
[TD="align: right"]07/01/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD]alan[/TD]
[TD]mike[/TD]
[TD]xavier[/TD]
[TD]fiona[/TD]
[TD]larry[/TD]
[TD]nancy[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]column=[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD]bill[/TD]
[TD]nick[/TD]
[TD]yolande[/TD]
[TD]gary[/TD]
[TD]mary[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]G[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD]colin[/TD]
[TD]oscar[/TD]
[TD]zack[/TD]
[TD]helen[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD]dave[/TD]
[TD]pete[/TD]
[TD]alf[/TD]
[TD]iris[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD]ed[/TD]
[TD]quentin[/TD]
[TD]brian[/TD]
[TD]james[/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="align: right"]6[/TD]
[TD]fred[/TD]
[TD]rick[/TD]
[TD]cedric[/TD]
[TD]keith[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]this is the macro that handled the booking requests[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD]george[/TD]
[TD]sam[/TD]
[TD]darren[/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="align: right"]8[/TD]
[TD]harry[/TD]
[TD]tom[/TD]
[TD]ethel[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Sub Macro4()[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]9[/TD]
[TD]ian[/TD]
[TD]ursula[/TD]
[TD]don[/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="align: right"]10[/TD]
[TD]joe[/TD]
[TD]veronica[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]' Macro4 Macro[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]11[/TD]
[TD]kevin[/TD]
[TD]william[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]' Macro recorded 16/11/2018 by bob[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]12[/TD]
[TD]len[/TD]
[TD]boris[/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]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 6"]below is the original table before copter bookings listed below[/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="colspan: 3"] If Cells(3, 3) > 0 Then GoTo 100[/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="colspan: 3"] Cells(6, 1) = "no seats available"[/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="colspan: 6"] For delay = 1 To 999999999: Next delay: Cells(6, 1) = ""[/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="colspan: 3"] Cells(1, 1) = "": Cells(1, 2) = ""[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD]alan[/TD]
[TD]mike[/TD]
[TD]xavier[/TD]
[TD]fiona[/TD]
[TD]larry[/TD]
[TD]nancy[/TD]
[TD] GoTo 500[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD]bill[/TD]
[TD]nick[/TD]
[TD]yolande[/TD]
[TD]gary[/TD]
[TD]mary[/TD]
[TD][/TD]
[TD="colspan: 5"]100 Cells(Cells(8, 3), Cells(7, 3)) = Cells(1, 2)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD]colin[/TD]
[TD]oscar[/TD]
[TD]zack[/TD]
[TD]helen[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"] Cells(6, 1) = "your seat has been reserved"[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD]dave[/TD]
[TD]pete[/TD]
[TD]alf[/TD]
[TD]iris[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"] For delay2 = 1 To 999999999: Next delay2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD]ed[/TD]
[TD]quentin[/TD]
[TD]brian[/TD]
[TD]james[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"] Cells(6, 1) = ""[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]6[/TD]
[TD]fred[/TD]
[TD]rick[/TD]
[TD]cedric[/TD]
[TD]keith[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"] Cells(1, 1) = ""[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD]george[/TD]
[TD]sam[/TD]
[TD]darren[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"] Cells(1, 2) = ""[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]8[/TD]
[TD]harry[/TD]
[TD]tom[/TD]
[TD]ethel[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]500 End Sub[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]9[/TD]
[TD]ian[/TD]
[TD]ursula[/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="align: right"]10[/TD]
[TD]joe[/TD]
[TD]veronica[/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="align: right"]11[/TD]
[TD]kevin[/TD]
[TD]william[/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="align: right"]12[/TD]
[TD]len[/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]note[/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]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]adrian[/TD]
[TD="align: right"]01/01/2018[/TD]
[TD][/TD]
[TD="colspan: 3"]was told no seats available[/TD]
[TD][/TD]
[TD="colspan: 4"]employees enter the date of their flight in A1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]boris[/TD]
[TD="align: right"]02/01/2018[/TD]
[TD][/TD]
[TD="colspan: 3"]was told his seat had been reserved[/TD]
[TD][/TD]
[TD="colspan: 2"]and their name in B1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]charlie[/TD]
[TD="align: right"]02/01/2018[/TD]
[TD][/TD]
[TD="colspan: 3"]was told no seats available[/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]don[/TD]
[TD="align: right"]03/01/2018[/TD]
[TD][/TD]
[TD="colspan: 3"]was told his seat had been reserved[/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]philip[/TD]
[TD="align: right"]03/01/2018[/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]graham[/TD]
[TD="align: right"]03/01/2018[/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]joanne[/TD]
[TD="align: right"]03/01/2018[/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]margaret[/TD]
[TD="align: right"]03/01/2018[/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]
[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]
[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]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]