Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning Columns to a List Box for a Holiday LookUp Table
Guys - I need help with this little problem! I have a worksheet that holds everyone's holiday period, that is the first day of their holiday in one cell and the last day of their holiday in another. The structure of this table is that row one holidays the employee name across two merged cells, this means that each employee has two columns in order to hold the dates of their holiday! The problem that I want to crack is through use of a user form, how do I return all of holiday dates to a list box when I search on a employee's name? I have created a form with a text box where I can enter in a name, and I can find the name using the Find function, but I am stuck as to how to copy the correct columns giving the start and end of the holiday. There could anything up to 200 names on the table. The other thing to note are that the dates (not the names) are linked to cells in other workbooks as each employee holds their own holidaysheet, and I think this may complicate the search. Can anyone help with this????? -- Noggy1 ------------------------------------------------------------------------ Noggy1's Profile: http://www.excelforum.com/member.php...o&userid=19126 View this thread: http://www.excelforum.com/showthread...hreadid=475489 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning Columns to a List Box for a Holiday LookUp Table
The links to other workbooks should not be a problem as long as everything is
up to date and calculated on the sheet you are using at the time you do the search. As for how to find the dates: If you have found the employee name (as you say you can) then (if I visualize this correctly) the start and end dates are directly beneath the merged cell with that name in it? The find function will return the address of the upper left cell that makes up the merged cells - in your case, this means that (address-wise) the start date is directly under the found cell, the end date is one cell to the right of that: StartDate = Range("1:1").Find(EmployeeName).Cells(2,1).Value EndDate = Range("1:1").Find(EmployeeName).Cells(2,2).Value I am not clear how you want to use these with your list box, but this is how you can find the dates. -- - K Dales "Noggy1" wrote: Guys - I need help with this little problem! I have a worksheet that holds everyone's holiday period, that is the first day of their holiday in one cell and the last day of their holiday in another. The structure of this table is that row one holidays the employee name across two merged cells, this means that each employee has two columns in order to hold the dates of their holiday! The problem that I want to crack is through use of a user form, how do I return all of holiday dates to a list box when I search on a employee's name? I have created a form with a text box where I can enter in a name, and I can find the name using the Find function, but I am stuck as to how to copy the correct columns giving the start and end of the holiday. There could anything up to 200 names on the table. The other thing to note are that the dates (not the names) are linked to cells in other workbooks as each employee holds their own holidaysheet, and I think this may complicate the search. Can anyone help with this????? -- Noggy1 ------------------------------------------------------------------------ Noggy1's Profile: http://www.excelforum.com/member.php...o&userid=19126 View this thread: http://www.excelforum.com/showthread...hreadid=475489 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup value in table without returning #N/A | Excel Discussion (Misc queries) | |||
Lookup info in one Column and then returning info in other columns | Excel Worksheet Functions | |||
Excel returning more than one value from a lookup table that match the same value | Excel Discussion (Misc queries) | |||
Returning a text result from 2 lookup columns | Excel Discussion (Misc queries) | |||
Returning multiple corresponding values using lookup in a list | Excel Discussion (Misc queries) |