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?
