Here's the easy way to do things so that the formulas get set up correctly,

all by Excel. From your last post, I'm assuming that there are two separate

..xls files involved in this, one with Report 1 in it and another with Report

2 in it.

Open both workbooks. Go to the workbook that you're going to put the

formula into and choose its sheet and select a cell in the first row you want

the VLOOKUP() formula to go into.

type =VLOOKUP(

at that point, click in the cell on that sheet (should probably on same row)

with the Item # in it that you want to look up and then type a comma (,)

choose the other workbook and the sheet with the report2 in it. Highlight

all of the cells in it that have the information in them that you need.

Based on your example earlier, that would be columns A through C, rows 1

through 4.

Type then finish out the formula by returning to the book you're putting the

formula into and typing

,3,0)

and pressing the [Enter] key.

At that point you should have a formula in the cell that looks something like:

=VLOOKUP(A2,'[Other Workbook]Report2Sheet'!$A$1:$C$4,3,0)

You can edit that formula to remove the $1 and $4 references in it and make

it refer to the entire columns:

=VLOOKUP(A2,'[Other Workbook]Report2Sheet'!$A:$C,3,0)

so that you don't have to keep changing it as you add more rows in the other

workbook later on.

How VLOOKUP works: It takes the first value you typed in (or the value in

the cell you first entered, A2, and then it looks in the first column of the

table you define as the second parameter for a match. It stops looking for a

match at the first one it finds, and if it does not find a match at all it

returns a #N/A! error. Once it finds a match it looks in the Nth column of

the table that you specified as the 3rd parameter. We entered ,3, as that

3rd parameters, so it will return a value from the same row that it matched

entries with out of the 3rd column of the table. If your table goes from

column A to column C (or even farther out like column F or AA), then the 3rd

column would be column C. If your table went from column D to column H, the

third column would be column F. That last parameter, which we typed in as

,0) this time is either TRUE or FALSE (or -1 or 0 numerically). By setting

it to 0/False, we tell VLOOKUP that the information in the first column is

not necessarily in any order, so look through the whole list until you find a

match or not.

NOTE: when you close that second workbook, the one Report 2 is in, Excel

will automatically add the path to that file into the formula, and when you

open that file in the future while Report 1 file is open, it disappears.

That's normal behavior. This is why it's easiest to set up these kinds of

formulas with both books open at the same time.

Hope this gets you a step or two closer to success.

Gameware said:

The "05" was a typo. I meant to say I'm using Office 2003.

I tried your formulas and was not successful getting them to work. I'm sure

it has to do with me being a novice when it comes to excel. I copied and

pasted the formula for 2 different worksheets into row 2 and a window opens

up asking me for a file name. I'm not sure what file I need to choose. I'm

also not sure if I'm supposed to place the file names I gave report 1 and 2

within the formulas you provided. Report 1 is named "test 1" and report 2 is

named "test 2".

Thanks for your help.

JLatham said:

I'm not sure where "O5" comes into things, unless that's where you want to

put your VLOOKUP() formula.

Assuming your data in Report 1 starts at A2, you could put this formula into

any cell on row 2 of the sheet with Report 1 (and assuming that Report 2 is

on another sheet in the workbook)

=VLOOKUP(A2,Sheet2!A:C,3,FALSE)

now that will return an ugly looking #N/A! error when there is an Item # in

column A in Report 1 that isn't in Report 2, so to keep things neat and clean:

=IF(ISNA(VLOOKUP(A2,Sheet2!A:C,3,FALSE)),"",VLOOKUP(A2,Sheet2!A:C,3,FALSE))

and if Report 2 is in another workbook, then it takes on this format:

=IF(ISNA(VLOOKUP(A2,'[Book2]Sheet2'!A:C,3,FALSE)),"",VLOOKUP(A2,'[Book2]Sheet2'!A:C,3,FALSE))

Hope this helps some.

Gameware said:

How would I combine these two reports together using 05?

Report 1 (Sales report)

Item # Description 1 Qty Sold Publisher

12 360 BLAZING ANGELS 1 UBISOFT

25 360 CIVIL WAR 1 ACTIVISION

43 360 ELDER SCROLLS IV 1 TAKE 2

71 360 GHOST RECON AW 2 1 UBISOFT

78 360 HARDWARE ARCADE 1 MICROSOFT

91 360 LIVE 1 MONTH CARD 1 MICROSOFT

94 360 LIVE 3 MONTH CARD 1 MICROSOFT

97 360 LIVE POINTS 1600 3 MICROSOFT

Report 2 (Qty On-hand)

Item # Description 1 On-Hand Publisher

12 360 BLAZING ANGELS 6 UBISOFT

25 360 CIVIL WAR 2 ACTIVISION

43 360 ELDER SCROLLS IV 4 TAKE 2

71 360 GHOST RECON AW 2 3 UBISOFT

I just need to add the on-hand qty from report 2 to report 1.

Report one only generates items that have sold which is normally several

hundred items. Report 2 will generate the entire inventory database which is

more than 10,000 items.

Do I have to use item # to use VLOOKUP? If Column A was a UPC # or

Description instead of item # how would I use VLOOKUP?