Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Offsetting and HLOOKUP
Here's what I've got set up:
A table of timesheet hours. The first row are date fields, which are merged cells spanning 4 columns. Underneath each date field, the individual columns are for different types of chargeable time: Reg, OT, DT, Travel. Down the table are rows for each employee. Now, on another worksheet, I want to populate the cells with the values off of the big "master" table. One worksheet for each day, with a date field at the top of the sheet which will match the dates at the top of the master table. Now, because the date fields are merged over 4 columns, when I use the HLOOKUP function, I can only pull out the values from the first column (of the 4). That's the Reg column. How do I go about HLOOKUP-ing the remaining columns? Is there someway to offset the cell that is returned by the HLOOKUP formula? Such that the formula for the OT column would be OFFSET(1 column, HLOOKUP(date,array,row#,false)). I know that's not a real formula, but that's what I'm trying to do. Thanks, KellyB |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Offsetting and HLOOKUP
What exactly are you "looking up" ?
The employee name for a certain date and a certain type? Or, the employee name for a certain date and *all* types? -- Biff Microsoft Excel MVP "KellyB" wrote in message ... Here's what I've got set up: A table of timesheet hours. The first row are date fields, which are merged cells spanning 4 columns. Underneath each date field, the individual columns are for different types of chargeable time: Reg, OT, DT, Travel. Down the table are rows for each employee. Now, on another worksheet, I want to populate the cells with the values off of the big "master" table. One worksheet for each day, with a date field at the top of the sheet which will match the dates at the top of the master table. Now, because the date fields are merged over 4 columns, when I use the HLOOKUP function, I can only pull out the values from the first column (of the 4). That's the Reg column. How do I go about HLOOKUP-ing the remaining columns? Is there someway to offset the cell that is returned by the HLOOKUP formula? Such that the formula for the OT column would be OFFSET(1 column, HLOOKUP(date,array,row#,false)). I know that's not a real formula, but that's what I'm trying to do. Thanks, KellyB |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Offsetting and HLOOKUP
I want to take the hours from the big data table, and populate separate
worksheets, one sheet for each day. I want to use the same sheet over and over again, I'll just change the date at the top, and all of the HLOOKUPs will populate the sheet with the data from the big table. My problem is that HLOOKUP will only grab the data from the first column (Regular time), and not from the other 3 adjacent columns. What I need is a way to offset, so that once HLOOKUP finds the correct set of 4 columns (because the date is at the top in a merged cell), then I can grab the data from all 4 columns, and not just the first one. Thanks, KB "T. Valko" wrote: What exactly are you "looking up" ? The employee name for a certain date and a certain type? Or, the employee name for a certain date and *all* types? -- Biff Microsoft Excel MVP "KellyB" wrote in message ... Here's what I've got set up: A table of timesheet hours. The first row are date fields, which are merged cells spanning 4 columns. Underneath each date field, the individual columns are for different types of chargeable time: Reg, OT, DT, Travel. Down the table are rows for each employee. Now, on another worksheet, I want to populate the cells with the values off of the big "master" table. One worksheet for each day, with a date field at the top of the sheet which will match the dates at the top of the master table. Now, because the date fields are merged over 4 columns, when I use the HLOOKUP function, I can only pull out the values from the first column (of the 4). That's the Reg column. How do I go about HLOOKUP-ing the remaining columns? Is there someway to offset the cell that is returned by the HLOOKUP formula? Such that the formula for the OT column would be OFFSET(1 column, HLOOKUP(date,array,row#,false)). I know that's not a real formula, but that's what I'm trying to do. Thanks, KellyB |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Offsetting and HLOOKUP
See if this sample file helps:
DeleteMe3.xls 15kb http://cjoint.com/?bDhzCFAh5T Change the date in B12 to get the desired data. -- Biff Microsoft Excel MVP "KellyB" wrote in message ... I want to take the hours from the big data table, and populate separate worksheets, one sheet for each day. I want to use the same sheet over and over again, I'll just change the date at the top, and all of the HLOOKUPs will populate the sheet with the data from the big table. My problem is that HLOOKUP will only grab the data from the first column (Regular time), and not from the other 3 adjacent columns. What I need is a way to offset, so that once HLOOKUP finds the correct set of 4 columns (because the date is at the top in a merged cell), then I can grab the data from all 4 columns, and not just the first one. Thanks, KB "T. Valko" wrote: What exactly are you "looking up" ? The employee name for a certain date and a certain type? Or, the employee name for a certain date and *all* types? -- Biff Microsoft Excel MVP "KellyB" wrote in message ... Here's what I've got set up: A table of timesheet hours. The first row are date fields, which are merged cells spanning 4 columns. Underneath each date field, the individual columns are for different types of chargeable time: Reg, OT, DT, Travel. Down the table are rows for each employee. Now, on another worksheet, I want to populate the cells with the values off of the big "master" table. One worksheet for each day, with a date field at the top of the sheet which will match the dates at the top of the master table. Now, because the date fields are merged over 4 columns, when I use the HLOOKUP function, I can only pull out the values from the first column (of the 4). That's the Reg column. How do I go about HLOOKUP-ing the remaining columns? Is there someway to offset the cell that is returned by the HLOOKUP formula? Such that the formula for the OT column would be OFFSET(1 column, HLOOKUP(date,array,row#,false)). I know that's not a real formula, but that's what I'm trying to do. Thanks, KellyB |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Offsetting and HLOOKUP
Wow, that's great. I never would have come up with that!
Thanks, KB "T. Valko" wrote: See if this sample file helps: DeleteMe3.xls 15kb http://cjoint.com/?bDhzCFAh5T Change the date in B12 to get the desired data. -- Biff Microsoft Excel MVP "KellyB" wrote in message ... I want to take the hours from the big data table, and populate separate worksheets, one sheet for each day. I want to use the same sheet over and over again, I'll just change the date at the top, and all of the HLOOKUPs will populate the sheet with the data from the big table. My problem is that HLOOKUP will only grab the data from the first column (Regular time), and not from the other 3 adjacent columns. What I need is a way to offset, so that once HLOOKUP finds the correct set of 4 columns (because the date is at the top in a merged cell), then I can grab the data from all 4 columns, and not just the first one. Thanks, KB "T. Valko" wrote: What exactly are you "looking up" ? The employee name for a certain date and a certain type? Or, the employee name for a certain date and *all* types? -- Biff Microsoft Excel MVP "KellyB" wrote in message ... Here's what I've got set up: A table of timesheet hours. The first row are date fields, which are merged cells spanning 4 columns. Underneath each date field, the individual columns are for different types of chargeable time: Reg, OT, DT, Travel. Down the table are rows for each employee. Now, on another worksheet, I want to populate the cells with the values off of the big "master" table. One worksheet for each day, with a date field at the top of the sheet which will match the dates at the top of the master table. Now, because the date fields are merged over 4 columns, when I use the HLOOKUP function, I can only pull out the values from the first column (of the 4). That's the Reg column. How do I go about HLOOKUP-ing the remaining columns? Is there someway to offset the cell that is returned by the HLOOKUP formula? Such that the formula for the OT column would be OFFSET(1 column, HLOOKUP(date,array,row#,false)). I know that's not a real formula, but that's what I'm trying to do. Thanks, KellyB |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Offsetting and HLOOKUP
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "KellyB" wrote in message ... Wow, that's great. I never would have come up with that! Thanks, KB "T. Valko" wrote: See if this sample file helps: DeleteMe3.xls 15kb http://cjoint.com/?bDhzCFAh5T Change the date in B12 to get the desired data. -- Biff Microsoft Excel MVP "KellyB" wrote in message ... I want to take the hours from the big data table, and populate separate worksheets, one sheet for each day. I want to use the same sheet over and over again, I'll just change the date at the top, and all of the HLOOKUPs will populate the sheet with the data from the big table. My problem is that HLOOKUP will only grab the data from the first column (Regular time), and not from the other 3 adjacent columns. What I need is a way to offset, so that once HLOOKUP finds the correct set of 4 columns (because the date is at the top in a merged cell), then I can grab the data from all 4 columns, and not just the first one. Thanks, KB "T. Valko" wrote: What exactly are you "looking up" ? The employee name for a certain date and a certain type? Or, the employee name for a certain date and *all* types? -- Biff Microsoft Excel MVP "KellyB" wrote in message ... Here's what I've got set up: A table of timesheet hours. The first row are date fields, which are merged cells spanning 4 columns. Underneath each date field, the individual columns are for different types of chargeable time: Reg, OT, DT, Travel. Down the table are rows for each employee. Now, on another worksheet, I want to populate the cells with the values off of the big "master" table. One worksheet for each day, with a date field at the top of the sheet which will match the dates at the top of the master table. Now, because the date fields are merged over 4 columns, when I use the HLOOKUP function, I can only pull out the values from the first column (of the 4). That's the Reg column. How do I go about HLOOKUP-ing the remaining columns? Is there someway to offset the cell that is returned by the HLOOKUP formula? Such that the formula for the OT column would be OFFSET(1 column, HLOOKUP(date,array,row#,false)). I know that's not a real formula, but that's what I'm trying to do. Thanks, KellyB |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to find out two offsetting nubmer in one column? | Excel Discussion (Misc queries) | |||
offsetting Secondary Axes to a cell value | Charts and Charting in Excel | |||
Tax- offsetting losses against profits | Excel Worksheet Functions | |||
Tax- offsetting losses against profits | Excel Worksheet Functions | |||
offsetting periods in chart | Excel Discussion (Misc queries) |