I have read hundreds of forum posts about this both at MrExcel and other places, so I'm sorry if this is already available but I really can't find it. I see that people here have a way to insert an actual Excel looking section in their posts but I don't know how to do that either so I'm going to have to fake this.
I have a table that holds a schedule. The first column lists a person's name. The next 42 colums are labeled by date. For example, the schedule might start on 3/22/2009 and run through 5/2/2009. For each person listed on the schedule, there will be an X if they work first shift, a Y if they work second shift, and a Z if they work second shift. If the person is off that day, the cell will be blank.
I want to be able to go to another worksheet preferably in another workbook), type a date in a certain field, and have it return the names and shift types of everyone who is working on that date, ideally sorted by shift (but this is not crucial).
I have tried every combination of lookup and get I can think of. I've tried using Microsoft Query, but it forces me to specify which column I'm querying, and that does me no good because I want it to be dynamic based on which date someone types in a specific field. Obviosuly if this were Access I would have had this work done six hours ago but the team I'm working with cannot use Access.
Any suggestions?
I have a table that holds a schedule. The first column lists a person's name. The next 42 colums are labeled by date. For example, the schedule might start on 3/22/2009 and run through 5/2/2009. For each person listed on the schedule, there will be an X if they work first shift, a Y if they work second shift, and a Z if they work second shift. If the person is off that day, the cell will be blank.
I want to be able to go to another worksheet preferably in another workbook), type a date in a certain field, and have it return the names and shift types of everyone who is working on that date, ideally sorted by shift (but this is not crucial).
I have tried every combination of lookup and get I can think of. I've tried using Microsoft Query, but it forces me to specify which column I'm querying, and that does me no good because I want it to be dynamic based on which date someone types in a specific field. Obviosuly if this were Access I would have had this work done six hours ago but the team I'm working with cannot use Access.
Any suggestions?
