Help with referencing please

[Matt]

I'll try and explain exactly how my spreadsheet is laid out and what i'm
trying to achieve.

The sheet is designed to log newspapers and wakeup calls that are ordered by
guests in a hotel. I have the newspaper side of things working as intended,
but the wakeup calls are giving me a headache.

It is laid out like this:-

Worksheet 1.

Columns A, B and C allocated to the ground floor.

Column A = Room numbers
Column B = requested Newspaper
Column C = Wakeup call


Columns D, E and F allocated to the first floor.

Column D = Room numbers
Column E = requested Newspaper
Column F = Wakeup call


Columns G, H and I allocated to the Second floor.

Column G = Room numbers
Column H = requested Newspaper
Column I = Wakeup call


Columns J, K and L allocated to the Third floor.

Column J = Room numbers
Column K = requested Newspaper
Column L = Wakeup call


Columns M, N and O allocated to the Fourth floor.

Column M = Room numbers
Column N = requested Newspaper
Column O = Wakeup call


Worksheet 2.

Column A has a list of times ranging from 4:00am to 11:00am at 5 minute
intervals. For example:-

4:00
4:05
4:10
4:15
etc. etc.

I what like the worksheet 2 to scan the wakeup call collumns in worksheet 1
(columns C, F, I, L and O) and return the room number next to the desired
times.

Hopefully, if possible, worksheet 2 would look something like this

.....
.....
TIME ROOM NUMBERS
6:00 79 170 176
6:05 75 171 189 190
6:10
6:15 73 289 391
6:20
6:25
6:30 74 181
6:35 474
6:40

Any advice would be greatly received

 

[Biff]

Your setup is not conducive to being able to do this.

Instead of setting up the floors side by side you should set them up to be
stacked on top of each other. Like this:

Ground
data
data

First
data
data
data

Second
data
data

How many rooms are there? You would need one formula per room for every 5
minute increment. So, from 4:00 to 11:00 is 85 5 minute increments times
total number of rooms.

Biff

 

[Epinn]

I am not trying to do exactly what Matt has in mind. But if my purpose is to get a list of room numbers for the calling time and I don't have to stick to a certain layout, I'll do the following.

Each record will have four fields.

Floor Room # Wake-up call at Newspaper (Y/N)

Then I'll do a sort by wake-up call and then by room #. I don't have to be bothered with formulas. It will look like this:-

Floor Room #. Wake-up call at Newspaper
1 102 6.00 Y
2 202 6.00 N
2 207 6.00 N
1 103 6.25 Y
2 203 6.25 N
0 12 6.50 Y
1 104 6.50 Y
2 204 6.50 N
2 206 6.50 N
1 101 7.00 Y
1 106 7.00 Y
2 201 7.00 N
1 107 7.25 Y
1 105 7.30 Y
2 205 7.30 N


Matt's layout is going across while mine is going down. Let me give it some more thought and see how my layout can be transformed to Matt's.

One can also hide the wake-up call column so that the newspaper column is right beside room #. Then feel free to do a sort by newspaper column.

All this may not serve Matt's purpose but I feel like picking my brain. Thank you for reading.

Epinn

I'll try and explain exactly how my spreadsheet is laid out and what i'm
trying to achieve.

The sheet is designed to log newspapers and wakeup calls that are ordered by
guests in a hotel. I have the newspaper side of things working as intended,
but the wakeup calls are giving me a headache.

It is laid out like this:-

Worksheet 1.

Columns A, B and C allocated to the ground floor.

Column A = Room numbers
Column B = requested Newspaper
Column C = Wakeup call


Columns D, E and F allocated to the first floor.

Column D = Room numbers
Column E = requested Newspaper
Column F = Wakeup call


Columns G, H and I allocated to the Second floor.

Column G = Room numbers
Column H = requested Newspaper
Column I = Wakeup call


Columns J, K and L allocated to the Third floor.

Column J = Room numbers
Column K = requested Newspaper
Column L = Wakeup call


Columns M, N and O allocated to the Fourth floor.

Column M = Room numbers
Column N = requested Newspaper
Column O = Wakeup call


Worksheet 2.

Column A has a list of times ranging from 4:00am to 11:00am at 5 minute
intervals. For example:-

4:00
4:05
4:10
4:15
etc. etc.

I what like the worksheet 2 to scan the wakeup call collumns in worksheet 1
(columns C, F, I, L and O) and return the room number next to the desired
times.

Hopefully, if possible, worksheet 2 would look something like this

.....
.....
TIME ROOM NUMBERS
6:00 79 170 176
6:05 75 171 189 190
6:10
6:15 73 289 391
6:20
6:25
6:30 74 181
6:35 474
6:40

Any advice would be greatly received

 

[Epinn]

Sorry, the alignment of the table was off. Let me try again.

Floor
Room #.
Wake-up call at
Newspaper

1
102
6.00
Y

2
202
6.00
N

2
207
6.00
N

1
103
6.25
Y

2
203
6.25
N

0
12
6.50
Y

1
104
6.50
Y

2
204
6.50
N

2
206
6.50
N

1
101
7.00
Y

1
106
7.00
Y

2
201
7.00
N

1
107
7.25
Y

1
105
7.30
Y

2
205
7.30
N



Epinn

I am not trying to do exactly what Matt has in mind. But if my purpose is to get a list of room numbers for the calling time and I don't have to stick to a certain layout, I'll do the following.

Each record will have four fields.

Floor Room # Wake-up call at Newspaper (Y/N)

Then I'll do a sort by wake-up call and then by room #. I don't have to be bothered with formulas. It will look like this:-

Floor Room #. Wake-up call at Newspaper
1 102 6.00 Y
2 202 6.00 N
2 207 6.00 N
1 103 6.25 Y
2 203 6.25 N
0 12 6.50 Y
1 104 6.50 Y
2 204 6.50 N
2 206 6.50 N
1 101 7.00 Y
1 106 7.00 Y
2 201 7.00 N
1 107 7.25 Y
1 105 7.30 Y
2 205 7.30 N


Matt's layout is going across while mine is going down. Let me give it some more thought and see how my layout can be transformed to Matt's.

One can also hide the wake-up call column so that the newspaper column is right beside room #. Then feel free to do a sort by newspaper column.

All this may not serve Matt's purpose but I feel like picking my brain. Thank you for reading.

Epinn

I'll try and explain exactly how my spreadsheet is laid out and what i'm
trying to achieve.

The sheet is designed to log newspapers and wakeup calls that are ordered by
guests in a hotel. I have the newspaper side of things working as intended,
but the wakeup calls are giving me a headache.

It is laid out like this:-

Worksheet 1.

Columns A, B and C allocated to the ground floor.

Column A = Room numbers
Column B = requested Newspaper
Column C = Wakeup call


Columns D, E and F allocated to the first floor.

Column D = Room numbers
Column E = requested Newspaper
Column F = Wakeup call


Columns G, H and I allocated to the Second floor.

Column G = Room numbers
Column H = requested Newspaper
Column I = Wakeup call


Columns J, K and L allocated to the Third floor.

Column J = Room numbers
Column K = requested Newspaper
Column L = Wakeup call


Columns M, N and O allocated to the Fourth floor.

Column M = Room numbers
Column N = requested Newspaper
Column O = Wakeup call


Worksheet 2.

Column A has a list of times ranging from 4:00am to 11:00am at 5 minute
intervals. For example:-

4:00
4:05
4:10
4:15
etc. etc.

I what like the worksheet 2 to scan the wakeup call collumns in worksheet 1
(columns C, F, I, L and O) and return the room number next to the desired
times.

Hopefully, if possible, worksheet 2 would look something like this

.....
.....
TIME ROOM NUMBERS
6:00 79 170 176
6:05 75 171 189 190
6:10
6:15 73 289 391
6:20
6:25
6:30 74 181
6:35 474
6:40

Any advice would be greatly received

 

[SteveW]

Yes have your data as
Column A = Floor
Column B = Room numbers
Column C = requested Newspaper
Column D = Wakeup call

Yes it means that A has "Ground" repeated in in each for the Ground Floor
but unless the room numbers include the floor you'll need that

You can look at your data by "Floor", by having a Filter on Row 1
(headings)
and in column A choose "First" - just shoing First floor info etc.

Now for the problem....

Still messy I'm off for a cup of tea
But at least all the data you'll need is in one column

bfn - will be back

was going to post above, but I've had my tea

I won't post it here but this is what I got that gives you the information

Plan - 2 stages, will have 2 macros (with buttons) for each

stage 1 - advanced filter on the data in A to extract all the rooms
that have a wakeup call

stage 2 - sort this extracted data by time, then floor
so you'll end up with a result table like

Wakeup Room Floor Paper
7:05 399 Third Yes
7:20 105 First No
8:00 350 Third No

It's not perfect but you end up with a sorted (by time) list

Stage 1 copy A11 to K1:N1
in F1:I1 put Wakeup, Room, Floor, Paper
F1:I1 will get the records with time
Put >0 into N1

Record the following (comments in brackets)
Data, Filter, Advanced Filter...
click on Copy to another location
(Set the 3 ranges to)
A120 K1:N2 and F1:I20 (change row 20 to fit your data)
Click OK, and Stop the macro
(I assigned this macro to a button - label *Get data*)

Stage 2
Record the following
Select F1:I20 (adjust for your data)
Data, Sort..
Sort by Wakeup, Room
Click OK, and Stop the macro
(I assigned this macro to a button - label *Sort it*)

Now keep the room info upto date, emter Wakeup times etc
Click on *Get data* then *Sort it*

Those are the basics



Steve

 

[Epinn]

The table is not a table. It looked so good on an email. May be someone can tell me how to paste a table from Word or a few columns from Excel on a post *properly aligned*.

Anyway, shall we write a macro to TRANSPOSE my format to Matt's format?

I may be totally off track and someone can do it with functions like VLOOKUP?

Epinn

Sorry, the alignment of the table was off. Let me try again.

Floor
Room #.
Wake-up call at
Newspaper

1
102
6.00
Y

2
202
6.00
N

2
207
6.00
N

1
103
6.25
Y

2
203
6.25
N

0
12
6.50
Y

1
104
6.50
Y

2
204
6.50
N

2
206
6.50
N

1
101
7.00
Y

1
106
7.00
Y

2
201
7.00
N

1
107
7.25
Y

1
105
7.30
Y

2
205
7.30
N

Epinn

I am not trying to do exactly what Matt has in mind. But if my purpose is to get a list of room numbers for the calling time and I don't have to stick to a certain layout, I'll do the following.

Each record will have four fields.

Floor Room # Wake-up call at Newspaper (Y/N)

Then I'll do a sort by wake-up call and then by room #. I don't have to be bothered with formulas. It will look like this:-

Floor Room #. Wake-up call at Newspaper
1 102 6.00 Y
2 202 6.00 N
2 207 6.00 N
1 103 6.25 Y
2 203 6.25 N
0 12 6.50 Y
1 104 6.50 Y
2 204 6.50 N
2 206 6.50 N
1 101 7.00 Y
1 106 7.00 Y
2 201 7.00 N
1 107 7.25 Y
1 105 7.30 Y
2 205 7.30 N


Matt's layout is going across while mine is going down. Let me give it some more thought and see how my layout can be transformed to Matt's.

One can also hide the wake-up call column so that the newspaper column is right beside room #. Then feel free to do a sort by newspaper column.

All this may not serve Matt's purpose but I feel like picking my brain. Thank you for reading.

Epinn

I'll try and explain exactly how my spreadsheet is laid out and what i'm
trying to achieve.

The sheet is designed to log newspapers and wakeup calls that are ordered by
guests in a hotel. I have the newspaper side of things working as intended,
but the wakeup calls are giving me a headache.

It is laid out like this:-

Worksheet 1.

Columns A, B and C allocated to the ground floor.

Column A = Room numbers
Column B = requested Newspaper
Column C = Wakeup call


Columns D, E and F allocated to the first floor.

Column D = Room numbers
Column E = requested Newspaper
Column F = Wakeup call


Columns G, H and I allocated to the Second floor.

Column G = Room numbers
Column H = requested Newspaper
Column I = Wakeup call


Columns J, K and L allocated to the Third floor.

Column J = Room numbers
Column K = requested Newspaper
Column L = Wakeup call


Columns M, N and O allocated to the Fourth floor.

Column M = Room numbers
Column N = requested Newspaper
Column O = Wakeup call


Worksheet 2.

Column A has a list of times ranging from 4:00am to 11:00am at 5 minute
intervals. For example:-

4:00
4:05
4:10
4:15
etc. etc.

I what like the worksheet 2 to scan the wakeup call collumns in worksheet 1
(columns C, F, I, L and O) and return the room number next to the desired
times.

Hopefully, if possible, worksheet 2 would look something like this

.....
.....
TIME ROOM NUMBERS
6:00 79 170 176
6:05 75 171 189 190
6:10
6:15 73 289 391
6:20
6:25
6:30 74 181
6:35 474
6:40

Any advice would be greatly received

 

[SteveW]

The first format was clear
But the idea is the same, move/plan data layout so that it can be used
easily
Bit like entering names as "Surname, FirstName" or better in 2 cells
So many put all the names in as "FirstName Surname" and then want to sort
by Surname

Cheers, Steve

 

[Epinn]

The first format was clear
But the idea is the same, move/plan data layout so that it can be used
easily
Bit like entering names as "Surname, FirstName" or better in 2 cells
So many put all the names in as "FirstName Surname" and then want to sort
by Surname

Cheers, Steve

 

[Matt]

Biff, Epinn, Steve. Thanks for you responses, this is all great stuff. I
don't really want to change the layout of my sheet, but i think i get around
this by creating a new worksheet that shows all the room numbers and calls in
columns as you suggested. The new sheet just pulls its data from my original
1. Then i can try what you have suggested by pulling the data from the newly
created sheet (does that make sense?). Anyhoo, i have just finished work
(9:30am here in the UK) and i am tired, so i am going to head off to bed and
i will have a bash at this tonight.

Thanks again guys, i appreciate your help. I'll keep you updated.

Matt

 

[Epinn]

Steve,

What idea? My idea of four columns and sorting which I posted **before** you did OR the idea of "copy and paste" to a post vs. email? I don't know if you are aware that I said my table (second one) looked okay in an *email*. In other words, I did plan my layout well. I just don't understand why it was all off when I pasted the exact same table to a *post*. Totally lost why posts always give me a problem when emails won't.

Of course, I have lots of experience in designing record layouts etc. although I may be new to Excel.

Cheers,

Epinn

The first format was clear
But the idea is the same, move/plan data layout so that it can be used
easily
Bit like entering names as "Surname, FirstName" or better in 2 cells
So many put all the names in as "FirstName Surname" and then want to sort
by Surname

Cheers, Steve

 

[SteveW]

The ida.. of re-organising the original data

Copy paste from web pages or word or even excel to a *post* usually gets
messed up with tabs and hidden *newlines*

Emails tend to support these better, leading spaces etc etc
never easy

Steve

 

[Epinn]

No, I didn't reorganize the original data. I just copied the cells from Excel to a table in Word hoping that the alignment will come out better. Unfortunately, it was worse. So, you thought the second table was another arrangement/reorganization. No, absolutely not. It was the exact same worksheet but pasting messed up the Word table (evolved from the worksheet).

I was wondering if it was because I set HTML for emails and plain text for posts. Won't worry for now.

Epinn

The ida.. of re-organising the original data

Copy paste from web pages or word or even excel to a *post* usually gets
messed up with tabs and hidden *newlines*

Emails tend to support these better, leading spaces etc etc
never easy

Steve

 

[SteveW]

I meant re-organize the original posters data

Won't worry for now.

We'll move onto the next puzzle.
Steve

 

[Matt]

OK, i've tinkered around with it, and i think i have a solution. I have
arranged all of the room numbers on a second sheet in column A and then used
formulas in column B to pull the wakeup calls from my original sheet. I have
used "IF" formulas in column A so that the room numbers only display if a
wakeup call is present. One more thing I would like to know now, is can I
set my new sheet to automatically sort by wakeup time? To be honest, i am
trying to make it a simple as possible for the staff. If I tell them that
thay have to highlight columns B then A and click on "Sort Ascending" i'm
sure I will only confuse them.

Cheers
Matt

 

[SteveW]

Provide them with a BIG button, with a macro behind it that does what you
want


Steve

 

[Matt]

ooooookay. Thanks so much for the help so far, but i am a complete noob when
it comes to macros. Everything i know about excel is what i have learnt by
playing with it (and a few things i have picked up from here)

I have created a LARGE button with the following macro behind it:-


Sub Sortcalls()
'
' Sortcalls Macro
' Macro recorded 09/09/2006 by Matthew.Porter
'

'
Columns("A:B").Select
Range("B1").Activate
Selection.SORT Key1:=Range("B1"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub



Now, That little lot to me may aswell be in chinese because it means nothing
to me. I did what i though was logical to create that, but when I click the
button, it does nothing, not even an error message. :S

any advice again is appreciated

Thanks
Matt

 

[SteveW]

Not much wrong with that as far as I can see.

Looks like my test one

When mine runs it leaves the sort area selected.

What happens with yours ?
Click on say C5, then run the macro
It should leave A:B selected

Steve

 

[Matt]

I believe i have it sorted now. The problem seemed to occur when i tried to
assign the macro to a button. So i assigned it to an image, and presto!!!!
Big thanks for all your help. )

Matt

 

[SteveW]

glad it worked
cheers for the feedback

Steve

I believe i have it sorted now. The problem seemed to occur when i
tried to
assign the macro to a button. So i assigned it to an image, and
presto!!!!
Big thanks for all your help. )

Matt