VLookUp or HLookUp Plus Index - Match, I think???

[Guest]

This one requires the Excel guru's or any one with an answer!

What I need to do is the following. I have part of it working already, but,
still miss additional information.

I have an application which contains data for employees names on a sheet
called "EmpData". The data is listed as column and cell A2 - Employee Name,
then, by month (Jan, Feb, March, Apr, May, Jun, Jul - Dec) In the columns F -
Q where the month are listed are Jan - Dec row 5 (Gross Income), row 6 = Fed
IncomeTax, row 7 = State IncomeTax, row 8 = Social Security, row 9 = Fica.

What I want or would like to do is select the employee name from a dropdown
combo box on a sheet called "Review" pull and have the selected target cells
populated with the respective data from a sheet called "EmpData", is this
possible? I guess that Index - Match may not be enough to pull everything I
need. I also need to have any blank vertical cells between the employees
names filtered out or dropped.

Any ideas or samples spreadsheet providing a solution will be welcomed!

I hope my explanation was clear, if not, post any questions you may have...

Thanks,...

 

[Guest]

One more thing guys, each employee record takes up to 4 rows per column for a
total 12 records = rows vertically!

Thanks,

 

[Biff]

I can't make heads or tails out of your data layout.

Biff

 

[L. Howard Kittle]

Hmmm,

Looks like a Vlookup/Sumproduct/Index/Match or ....?

I would be glad to look at a sample worksheet/workbook to see if I can offer
a solution.

(e-mail address removed)

Include as much detailed info about what you want to happen to what cells
when you do the drop down selection.

Regrds,
Howard

 

[Roger Govier]

Hi Jay

If I understand you correctly, there is an employee name in A2, then
various headings till we get to A10 where the next employee name occurs,
then A18 and so on.

If this is the case, you could insert a new column A and in cell A2
enter
=IF(MOD(ROW()+6,8)=0,B2,A1)
and fill down for the extent of your data.
This will fill in the employee name against each row of their data in
your multi-line record.
Now, place your cursor in cell A1 and choose Data>Filter>Autofilter and
use the dropdown to select the Employee required and you will see a
filtered list of just his/her entries.

IF you do need the data to be on a separate sheet, repeat the steps
above to create a new column A, and use Advanced Filter instead to
extract data to another sheet.
For instruction on the use of Advanced Filter, take a look at the
excellent instructions at Debra Dalgleish's site
http://www.contextures.com/xladvfilter01.html#ExtractWs

If your data is not every 8 rows, then amend the formula accordingly.
In cell A2, ROW() will return a value of 2, so adding 6 will give 8 and
the MOD function dividing by 8 will leave a remainder of 0. If it is
every 7 rows, then change to MOD(Row()+5,7) and so on.

 

[Guest]

Thanks, Roger but that is NOT what I am looking for. What I need is to create
a cross section using a sort of HLookUp and VLookUp function. HLookUp to find
a Employee name on a specific row, then use the VLookUp to pick up specific
data for the employee four (4) rows down at the intersection point! IS that
possible? If it is, how can I get it done?

 

[Roger Govier]

Hi Jay

I'm still finding it difficult to understand the layout of your data.
Could you post a sample of data for at least 2 employees, so we can get
an idea of the layout.
I'm not worried by all the columns going across, Jan to Dec, just data
as far as the Jan column will be quite sufficient.
When se see the exact layout, I'm sure someone will be able to come up
with a solution for you.

 

[Biff]

See your other post.

Biff

Thanks, Roger but that is NOT what I am looking for. What I need is to
create
a cross section using a sort of HLookUp and VLookUp function. HLookUp to
find
a Employee name on a specific row, then use the VLookUp to pick up
specific
data for the employee four (4) rows down at the intersection point! IS
that
possible? If it is, how can I get it done?

 

[Guest]

Okay, here goes, the data listed goes from column A - L (it's kinda messed up
in this screen. You may need to drop it back into excel for a proper view of
the lay out. I hope this HELPS!:


Javier Garza 1380.00
Income Tax / Loon Belaasting
AOV / AWW = Employee(4%) 0.00 55.20
AZV = Employee (1.6%) 0.00 22.08
Totals
Juan Mattos 1250.00 1350.00 1400.00
Income Tax / Loon Belaasting
AOV / AWW = Employee(4%) 50.00 54.00 56.00
AZV = Employee (1.6%) 20.00 21.60 22.40
Totals
Miguel Perez 1350.00 1350.00 1400.00
Income Tax / Loon Belaasting
AOV / AWW = Employee(4%) 54.00 54.00 56.00
AZV = Employee (1.6%) 21.60 21.60 22.40
Totals
Pablo Montana 1400.00 1380.00
Income Tax / Loon Belaasting
AOV / AWW = Employee(4%) 56.00 55.20
AZV = Employee (1.6%) 22.40 22.08
Totals

 

[Guest]

Biff:

Thank you so very much. That's exactly what I was looking for. Now, I need
to point the Index to the "Emp Data" sheet. Can I use a named range in this
case? I'm pretty sure I can, just wondering?

Thanks again, it worked great!

 

[Biff]

I need to point the Index to the "Emp Data" sheet.

Can I use a named range in this case?

Yes.

Biff

 

[Guest]

Biff, please take a look at my/your later post. I have a question there for
you!