managing contacts

[wizard]

Good afternoon,

For my work at a resorts' reception desk I am trying to improve the functionality and usage of MS excel.

At the moment there are a lot of different spreadsheets which all need to be constantly edited and printed which takes a lot of time.

There is 1 sheet where we manage all the current guests that are in the resort. Each party has an own room number assigned to the last name.
For the check-outs we always have to re-type the room number, name of the guest, etc.

My question: Is it possible that whenever I fill in a room number in spreadsheet 1's ''room #" colum, excel will automatically obtain the name of the guest that is located in spreadsheet 2 next to the same room number?

Example:
Spreadsheet 2:
Room number ---- guest name
1000----Johnson

Spreadsheet 1:
Room number ---- guest name
<I fill 1000 in here and at 'guest name' ''Johnson'' appears.

If this is not clear I will post a screenshot to make it clear.
Thanks in advance,

David

[Cheeky Charlie]

Um, I can try to answer your question, but couldn't you, instead of this, check your customers in and out on the same sheet? If I understand you, wouldn't that save more time?

CC

[wizard]

Um, I can try to answer your question, but couldn't you, instead of this, check your customers in and out on the same sheet? If I understand you, wouldn't that save more time?

CC

Charlie,

Thanks for reading.
The reason this goes in a seperate file is that we require a short list with all the check-outs of a certain day so we can check whether they have open bills or extra items to hand in at their departure.

If we would do this on the same list as where we manage the present guests, the list would be 3 pages which is a waste of both paper and functionality.

I hope you can help me with this.

Edit:

We only have 1 PC at the front desk which explains why we use a small printed list with all the check-outs. The management is kind of saving on technological expenses ;-)

Regards

[Cheeky Charlie]

It would probably help if you could upload examples (xls rather than images); I should have asked for that first rather than trying to guess. It doesn't need to be massive, just the top ten rows of each sheet, and *please* remove any personally identifiable information.

CC

[wizard]

Currently I'm at home already. Attached you can find an example of what it looks like.

It would be great if all details would show up in sheet 2 when I insert the room number only.

Edit.
It would be even better if it would automatically select all the persons that depart at the current day instantly and put it in page 2.

[wizard]

Any help would be great.

[shg]

Use col A as a helper column (you can hide it).

In Page2!A4 and copy down, =IF(C4="", "", MATCH(C4, 'Page 1'!A:A, 0) )

In Page2!B3 and down, =IF($A4 = "", "", INDEX('Page 1'!B:B, $A4) )

In Page2!D3 and down, =IF($A4 = "", "", INDEX('Page 1'!C:C, $A4) )

In Page2!L4 and down, =IF($A4 = "", "", INDEX('Page 1'!E:E, $A4) )

You get the idea ...

[wizard]

Use col A as a helper column (you can hide it).

In Page2!A4 and copy down, =IF(C4="", "", MATCH(C4, 'Page 1'!A:A, 0) )

In Page2!B3 and down, =IF($A4 = "", "", INDEX('Page 1'!B:B, $A4) )

In Page2!D3 and down, =IF($A4 = "", "", INDEX('Page 1'!C:C, $A4) )

In Page2!L4 and down, =IF($A4 = "", "", INDEX('Page 1'!E:E, $A4) )

You get the idea ...

Shg,

First of all, thanks.

I'm sorry I am not familiar with this function.
Could you apply it in my sheet as an example and reupload it?

Thanks & regards,

David

[shg]

You don't need to be familiar with it to copy the formulas from the post and paste them into the specified cells, or to look it up in Help.