ExcelBanter - Spreadsheets with multiple entries

You could also look at the combination of MATCH and INDEX, which could give
you a more flexible, and possibly more maintainable solution.

Assuming you have a manufacturers parts sheet, add a column to your Parts
list sheet to contain an "index" number. Assume you are putting your part
number (hopefully equal to the manufacturer's part number) in column A,
starting in A2 - headers in row 1. Assume Manufacturers Part Number is in
Sheet1, colum A, and there is nothing else in that column. In the index
column on your parts list, row 2, put

=if(A2<"",MATCH(A2,Sheet1!A:A,0),"")

This will give you the row number of that part number in Sheet1. If A2 is
blank you will get a blank in your index column. If the part number doesn't
exist you will get #N/A.

Assume your index column is column B (you can hide it later if you don't
want to see it) and assuming the vendor dexcription is in Column D in sheet
1, in your description column, row 2, put

=IF(A2="","",IF(ISNUMBER(B2),INDEX(Sheet1!D:D,B2), "Invalid Number"))

This should carry forward the description or and leave it blank if there is
nothing in A2. If there is something in A2 and the number doesn't match
anything in the vendor's list, the description will say "Invalid Number"

In other columns you want to bring forward put

=IF(ISNUMBER(B2),INDEX(Sheet1!X:XD,B2),""), where sheet 1 column X contains
the attribute you want.

The MATCH and INDEX approach has the advantage that if you ever need to
insert columns in the source (vendor's) sheet, or move existing columns in
the source sheet around by cutting and inserting, your formulas will still
work. You could also minimize those propblems with VLOOKUP, but you would
need to use approaches that are not generally publicized, and certainly not
shown in MS Excel Help, at least not in the version I have used, which are no
newer than 2002.

If you have many vendors on sheet 1 or you have many different sheets, one
for each vendor, for your source data, then you need a more complex approach.
Come back if you need that.

Also, please note that I have simply written these formulas out and not
tested and copied them from a test sheet, so if they don't work exactly right
it may be due to some omitted or misplaced parentheses - let me know if you
have a problem.

HTH

Declan O'R

"Confused in Virginia" wrote:

I am creating a "parts" list, gives part number, discription, and cost, from
a particular vendor, I would like to know if i can just enter the part number
and the rest of the cells will fill themselves in automaticlly.