Displaying Data from 2 workbooks

GrassHopper

New Member
Joined
Oct 17, 2002
Messages
9
I have been unsuccessful in my attempts at this and thought I would ask the experts.
What I have are to files, one is the planned employee hours for the week, the other is the actual. Each sheet includes the Location of the employee. What I am trying to do is with another sheet enter in a Location which brings in the plan for that location and the actual for the location.
 

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"

gplhl

Board Regular
Joined
Feb 23, 2002
Messages
148
Use VLOOKUP to lookup the data you want, using the name for the Lookup.



_________________
Regards,

Gary Hewitt-Long
This message was edited by gplhl on 2002-10-21 18:09
 

GrassHopper

New Member
Joined
Oct 17, 2002
Messages
9
Thanks for your Help with this.
This message was edited by GrassHopper on 2002-10-22 20:16
 

GrassHopper

New Member
Joined
Oct 17, 2002
Messages
9
I have run into a problem using the Vlookup,
The sheet I want to bring data from is organized by location(1,2,3,4) in column A.

location 1 takes up 5 rows, location 2 takes up 4 ect. When I look up location 2, the first 5 rows all show the first row for location 2, then the rest are ok.

My code looks something like this and is filled into a range of cells

F3 =VLOOKUP($E$3,Actual!A1:$W$1010,1,0)
F4 =VLOOKUP($E$3,Actual!A2:$W$1010,1,0)

I have tried to manipulate this but have not got it to work, any ideas?
This message was edited by GrassHopper on 2002-10-22 19:50
 

Juan Pablo González

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959

ADVERTISEMENT

The VLOOKUP formula works by looking the value in the first parameter (E3 in your case), in a matrix or range (Actual!A1:$W$1010 in your example), and returning the "column" specified in the third parameter (1).

But, you're saying that you have your location set up in rows... so, VLOOKUP shouldn't apply there... so, can you post some example data of how you're really set up and what you want as results ?
 

GrassHopper

New Member
Joined
Oct 17, 2002
Messages
9
What I have are 2 sheets Specific and Company.

In the company sheet I have detail of hours worked with a heading in Row 1, column 1 = location (1,2,3 ect), column 2 = account, column 3 = employee name, column 4 = hours.

In the specific sheet I want to display the detail for a location that is input into A1,
ie type 1 into A1 for location 1.

So what I get is I can come to the specific sheet type in a location, and the detail for that location will appear in the specific sheet.

Is this clear?
What I am trying to do is to get away from a filter, because I would also like to bring in detail of what was planned for the specific Location so they can be compared side by side.
 

BigC

Well-known Member
Joined
Aug 4, 2002
Messages
851

ADVERTISEMENT

Depending on the contents of your data table on the Company sheet, VLOOKUP may not be the function you need. VLOOKUP is best suited to situations where a table holds unique values in the key field, so if you have more than one occurence of an item, say location 1, in your table, then VLOOKUP is not the answer. Use SUMIF to give a total for the selected location, or some other solution (perhaps filters or VBA) if you need to list all occurences of that location returned from the Company sheet to your Specific sheet.

HTH
 

GrassHopper

New Member
Joined
Oct 17, 2002
Messages
9
Thanks for the reply BigC.
vlookup isn't working and sumif doesn't give me what I want. I'm thinking there has to be a different solution to using a filter. I just haven't been able to figure it out.
 

Spit Fire

New Member
Joined
Nov 30, 2002
Messages
4
Hi there, I am also facing the almost alike problem. In my company, every employee have their own time sheet and at the end of the month they much submit all of their time sheet to me so that I can collect all their information about the project they are working on and time usage.

Question: How can compare their worksheet? for example, in worksheet 1, i have a project name "Project1" in cell A7, while in worksheet2, the project name(Project1) is in cell A12,what i need to do now is in worksheet3, i need to get the value of Project1 in both of the sheet. how i suppose to compare and match the both name and get the value in worksheet3.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
On 2002-12-01 23:13, Spit Fire wrote:
Hi there, I am also facing the almost alike problem. In my company, every employee have their own time sheet and at the end of the month they much submit all of their time sheet to me so that I can collect all their information about the project they are working on and time usage.

Question: How can compare their worksheet? for example, in worksheet 1, i have a project name "Project1" in cell A7, while in worksheet2, the project name(Project1) is in cell A12,what i need to do now is in worksheet3, i need to get the value of Project1 in both of the sheet. how i suppose to compare and match the both name and get the value in worksheet3.

See http://216.92.17.166/board/viewtopic.php?topic=23775&forum=2&start=10&10

You can better start your own thread (if the quoted thread does not supply what you need).
 

Watch MrExcel Video

Forum statistics

Threads
1,122,232
Messages
5,594,956
Members
413,954
Latest member
mrsandy

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