Offsetting and HLOOKUP

K

KellyB

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
 
T

T. Valko

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?
 
K

KellyB

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Hlookup 0
HLOOKUP & VLOOKUP Combinded 4
Hlookup with Merged cell as look up value 1
Setting column width in a table with a macro is not working 0
Data Validation, Hlookup saving data issues 0
Format column widths from one table to a separate table on same page 1
Hlookup, range_lookup parameter 1
HLOOKUP using variable columns 5

Top