VLOOKUP???

darkstar0

New Member
Joined
Mar 23, 2004
Messages
2
I am new to working in Excel and I am trying to come up with a format that will allow entries of all workers' and their hours on a main sheet, which can then be viewed under a sheet specific to them. There should only be 2 columns of data to search. One of the employee, and the next of hours worked. I have tried using a VLOOKUP formula, but I am getting #N/A or repeated data entries. I have tried reading allot of the posts here for an answer, but they are beyond this level. I know this is probably elementary, but I'm stumped. Please set me on the right track here.

Thanks in advanced.
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

P Sitaram

Well-known Member
Joined
Jun 24, 2003
Messages
1,974
Welcome to the Board!

Can you give more details of your data and the formula that you are using? It will also be useful if you post a part of the w/s using Colo's utility.
 

darkstar0

New Member
Joined
Mar 23, 2004
Messages
2
This is a look at what I am getting. Tammy only has two entries, but shows 6 on her sheet followed by #N/A. The formula I am using is:
=VLOOKUP("tammy",Sheet1!A2:C130,2,FALSE)

Worksheet 1Employee Hours
Tammy 6
Joe 5.5
Evelyn 6
Tanya 3.5
Adam 4
Tammy 2
Ethan 7

Sheet 2 "Tammy's
Tammy
6
2
2
2
2
2
#N/A
#N/A
#N/A


I appreciate it.
 

P Sitaram

Well-known Member
Joined
Jun 24, 2003
Messages
1,974
I think in copying the formula down the rows the reference to the source range would have changed relatively. Hence, the above results.

In any case, the approach you are using will not serve your objective. If i understand you correctly, you want to extract all the rows pertaining to each employee to a separate worksheet. In that case, try filtering the source data (Data | Filter) and copying the filtered data to the relevant sheet.
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638

ADVERTISEMENT

Hi,

I think you need something like this:

This formula must be entered with Ctrl + Shift + Enter.
Book1
ABCD
1EmployeeHoursTammy
2Tammy66
3Joe5.52
4Evelyn6 
5Tanya3.5 
6Adam4 
7Tammy2 
8Ethan7 
Sheet2
 

Fergus

Well-known Member
Joined
Mar 10, 2004
Messages
1,174
Hi, An alternate solution which is simpler but needs a two stage approach would be to enter the formula: "=IF(ISNA(VLOOKUP("Tammy",Sheet1!A2:C2,2,FALSE)),"",VLOOKUP("Tammy",Sheet1!A2:C2,2,FALSE))" (without the quotes) into cell A2 of Tammy's sheet (Sheet2) and fill down for the same number of rows as you have in the composite sheet1- which looks as though it should be to row 130 from your initial formula. This will effectively reduce the array you are searching to each line on Sheet1 and will return Tammy's hours on the same line in Sheet2 in which they appeared on the original composite Sheet1. Then, second stage, highlight column A on Tammy's sheet and sort (Go to Data, Sort, click to include header row and sort in ascending order).
 

Watch MrExcel Video

Forum statistics

Threads
1,122,186
Messages
5,594,738
Members
413,929
Latest member
Hypatia

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top