Auto populate from list on separate sheet

[DukeNova]

I work in a hotel and I'm working on a system to improve the fluidit
between the front desk and housekeeping departments.

On one sheet I have a list of every room number and then the room typ
in the adjacent column. ie A1: 800, B1: NPK.

What I need now is when I'm on a separate sheet and I put in 800 in th
Room column that it will auto populate "NPK" into the adjacent typ
column.

I've tried a few things already but nothing's worked yet. Any help woul
be appreciated

 

[lhkittle]

I work in a hotel and I'm working on a system to improve the fluidity

between the front desk and housekeeping departments.



On one sheet I have a list of every room number and then the room type

in the adjacent column. ie A1: 800, B1: NPK.



What I need now is when I'm on a separate sheet and I put in 800 in the

Room column that it will auto populate "NPK" into the adjacent type

column.



I've tried a few things already but nothing's worked yet. Any help would

be appreciated.

Hi DukeNova,

Try this in B1:
=LOOKUP(A1,{1,2,3,4,800},{"AA","BB","CC","DD","NPK"})

Where you will change the {1,2,3,4,800} to the values to suit your hotel needs AND these Lookup Values MUST be in accending order.

The Lookup Array does not, as in this example.
=LOOKUP(A1,{1,"A",3,"B",800},{"ZZ","BB","CC","AA","NPK"})

HTH
Regards,
Howard

 

[lhkittle]

I work in a hotel and I'm working on a system to improve the fluidity

between the front desk and housekeeping departments.



On one sheet I have a list of every room number and then the room type

in the adjacent column. ie A1: 800, B1: NPK.



What I need now is when I'm on a separate sheet and I put in 800 in the

Room column that it will auto populate "NPK" into the adjacent type

column.



I've tried a few things already but nothing's worked yet. Any help would

be appreciated.

I may have read your request wrong. You might want something like this on the other sheet, where you enter the 800 in C1 and the formula returns fromthe Table Array on the first sheet. The 2 is the second column of that array and the 0 returns an exact match.

=VLOOKUP(C1,Sheet1!A1:B9,2,0)

Howard

 

[Spencer101]

I work in a hotel and I'm working on a system to improve the fluidit
between the front desk and housekeeping departments.

On one sheet I have a list of every room number and then the room typ
in the adjacent column. ie A1: 800, B1: NPK.

What I need now is when I'm on a separate sheet and I put in 800 in th
Room column that it will auto populate "NPK" into the adjacent typ
column.

I've tried a few things already but nothing's worked yet. Any help woul
be appreciated.

VLOOKUP is what you need.

Have a look at the yellow cell on sheet 2 in the attached.

If you need explanation of how it works, just let me know.

S

+-------------------------------------------------------------------
|Filename: DukeNova Example.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=575
+-------------------------------------------------------------------