If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Spreadsheets with multiple entries
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. |
#2
|
|||
|
|||
Spreadsheets with multiple entries
Take a look at the VLOOKUP function
-- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------*------------------------------*---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------*------------------------------*---------------- "Confused in Virginia" Confused in wrote in message ... 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. |
#3
|
|||
|
|||
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,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. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Reduce multiple entries on reports from Access DB | Reduce multiple entries for same person | Setting Up & Running Reports | 1 | August 29th, 2005 06:16 AM |
how to enter and store multiple entries | Boze | New Users | 2 | August 23rd, 2005 05:07 PM |
Multiple subgroup entries for one person | Miked | Database Design | 1 | April 6th, 2005 06:06 PM |
creating a table without multiple entries | talktobatchu | Database Design | 1 | November 10th, 2004 02:39 AM |
Multiple Many-To-Many Tables | Tom | Database Design | 7 | May 15th, 2004 03:47 AM |