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]

col C

1

2

3

4

5

6

7

12

12

9

6

2

1

6

<tbody> </tbody>

seats avail

#N/A

01/01/2018

02/01/2018

03/01/2018

04/01/2018

05/01/2018

06/01/2018

07/01/2018

#N/A

1

alan

mike

xavier

fiona

larry

nancy

column=

#N/A

2

bill

nick

yolande

gary

mary

G

3

colin

oscar

zack

helen

#N/A

4

dave

pete

alf

iris

#N/A

5

ed

quentin

brian

james

6

fred

rick

cedric

keith

this is the macro that handled the booking requests

7

george

sam

darren

8

harry

tom

ethel

Sub Macro4()

9

ian

ursula

don

'

10

joe

veronica

' Macro4 Macro

11

kevin

william

' Macro recorded 16/11/2018 by bob

12

len

boris

'

below is the original table before copter bookings listed below

'

If Cells(3, 3) > 0 Then GoTo 100

Cells(6, 1) = "no seats available"

`

For delay = 1 To 999999999: Next delay: Cells(6, 1) = ""

Cells(1, 1) = "": Cells(1, 2) = ""

1

alan

mike

xavier

fiona

larry

nancy

GoTo 500

2

bill

nick

yolande

gary

mary

100 Cells(Cells(8, 3), Cells(7, 3)) = Cells(1, 2)

3

colin

oscar

zack

helen

Cells(6, 1) = "your seat has been reserved"

4

dave

pete

alf

iris

For delay2 = 1 To 999999999: Next delay2

5

ed

quentin

brian

james

Cells(6, 1) = ""

6

fred

rick

cedric

keith

Cells(1, 1) = ""

7

george

sam

darren

Cells(1, 2) = ""

8

harry

tom

ethel

500 End Sub

9

ian

ursula

10

joe

veronica

11

kevin

william

12

len

note

adrian

01/01/2018

was told no seats available

employees enter the date of their flight in A1

boris

02/01/2018

was told his seat had been reserved

and their name in B1

charlie

02/01/2018

was told no seats available

don

03/01/2018

was told his seat had been reserved

philip

03/01/2018

graham

03/01/2018

joanne

03/01/2018

margaret

03/01/2018

<colgroup><col><col span="2"><col span="2"><col span="2"><col span="2"><col><col><col><col span="5"></colgroup><tbody>
</tbody>