Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help with referencing please
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help with referencing please
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 "Matt" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help with referencing please
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 "Matt" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help with referencing please
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 "Epinn" wrote in message ... 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 "Matt" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help with referencing please
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:D 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 A1:D1 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) A1:D20 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 On Fri, 08 Sep 2006 06:34:12 +0100, Biff wrote: 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 "Matt" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help with referencing please
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 "Epinn" wrote in message ... 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 "Epinn" wrote in message ... 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 "Matt" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help with referencing please
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 On Fri, 08 Sep 2006 08:26:40 +0100, Epinn wrote: 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 "Epinn" wrote in message ... 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 "Matt" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help with referencing please
"SteveW" wrote in message news:op.tfjuooj7evjsnp@enigma03... 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 On Fri, 08 Sep 2006 08:26:40 +0100, Epinn wrote: 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 "Epinn" wrote in message ... 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 "Matt" wrote in message ... 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 |
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help with referencing please
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 "SteveW" wrote: 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:D 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 A1:D1 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) A1:D20 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 On Fri, 08 Sep 2006 06:34:12 +0100, Biff wrote: 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 "Matt" wrote in message ... 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 |
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help with referencing please
Steve,
But the idea is the same...... 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. ......move/plan data layout so that it can be used easily. Of course, I have lots of experience in designing record layouts etc. although I may be new to Excel. Cheers, Epinn "SteveW" wrote in message news:op.tfjuooj7evjsnp@enigma03... 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 On Fri, 08 Sep 2006 08:26:40 +0100, Epinn wrote: 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 "Epinn" wrote in message ... 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 "Matt" wrote in message ... 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 |
#11
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help with referencing please
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 On Fri, 08 Sep 2006 09:36:22 +0100, Epinn wrote: Steve, But the idea is the same...... 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. ......move/plan data layout so that it can be used easily. Of course, I have lots of experience in designing record layouts etc. although I may be new to Excel. Cheers, Epinn "SteveW" wrote in message news:op.tfjuooj7evjsnp@enigma03... 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 On Fri, 08 Sep 2006 08:26:40 +0100, Epinn wrote: 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 "Epinn" wrote in message ... 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. |
#12
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help with referencing please
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 "SteveW" wrote in message news:op.tfjw89dvevjsnp@enigma03... 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 On Fri, 08 Sep 2006 09:36:22 +0100, Epinn wrote: Steve, But the idea is the same...... 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. ......move/plan data layout so that it can be used easily. Of course, I have lots of experience in designing record layouts etc. although I may be new to Excel. Cheers, Epinn "SteveW" wrote in message news:op.tfjuooj7evjsnp@enigma03... 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 On Fri, 08 Sep 2006 08:26:40 +0100, Epinn wrote: 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 "Epinn" wrote in message ... 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 "Matt" wrote in message ... 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 |
#13
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help with referencing please
I meant re-organize the original posters data
Won't worry for now. We'll move onto the next puzzle. Steve On Fri, 08 Sep 2006 10:05:54 +0100, Epinn wrote: 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 |
#14
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help with referencing please
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 "Matt" wrote: 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 |
#15
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help with referencing please
Provide them with a BIG button, with a macro behind it that does what you
want Steve On Sat, 09 Sep 2006 00:41:01 +0100, Matt wrote: 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 "Matt" wrote: 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 -- Steve (3) |
#16
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help with referencing please
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) :P 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" wrote: Provide them with a BIG button, with a macro behind it that does what you want Steve |
#17
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help with referencing please
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 On Sat, 09 Sep 2006 04:24:01 +0100, Matt wrote: 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) :P 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" wrote: Provide them with a BIG button, with a macro behind it that does what you want Steve -- Steve (3) |
#18
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help with referencing please
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" wrote: 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 |
#19
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help with referencing please
glad it worked
cheers for the feedback Steve On Mon, 11 Sep 2006 07:38:02 +0100, Matt wrote: 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I display a list of all cells referencing a particular cel | Excel Worksheet Functions | |||
Using vlookup when referencing text | Excel Discussion (Misc queries) | |||
Harvard Referencing System for books | New Users to Excel | |||
How do I turn off relative cell referencing in excel? | New Users to Excel | |||
Relative Indirect Formula Referencing? | Excel Worksheet Functions |