Excel 2003 Conditional cells

Microsoft Excel 2003 (full product)
November 17, 2009 at 14:31:38
Specs: Windows XP
I have a Parts spreadsheet that I want to be able to fill in the first column in a row, which is the part number, and then it automatically fills in the other columns in the row, which are connector, speed, size, manufacturer, etc. Should I use a macro for this? Should I create a VBA class module for each different type of part, hds, RAM, motherboards, etc?

See More: Excel 2003 Conditional cells

Report •

November 17, 2009 at 15:10:32

You should be able to do this with standard formulas, with no need for macros.

The VLOOKUP() function allows you to lookup a value (the lookup value) in a table and return a matching item.

If your table has the part number in the left most column, then the various elements such as connector, speed, size, manufacturer etc. in subsequent columns, VLOOKUP will find the part number and return values from any column to the right in the table.

As an example, if you have your table on Sheet2 with part number in column A (say 50 parts, so A1 to A50), with connector in column B (B1 to B50), and so on with say cost as the last element in Column G (G1 to G50),

then enter a part number in cell A1, and this formula in cell A2 will return the cost:


The 7 in the formula is because the result, in this case cost, is in column G which is the seventh column in the table

If the part number is not present it returns #NA, but this can be dealt with by a modification of the formula. See this :
recent post.

BTW for most macros, you use a standard module, not a class module.

Report •

November 17, 2009 at 15:17:20
If all the data is in a table, you could use VLOOKUP to populate the other columns.

If your data table looked like this...

     A        B           C            D                 E
1  P/N    Connector     Speed         Size          Manufacturer
2  123      DB-25       55 MPH    Ladies' Small         GM
3  234       USB         Slow      Men's Large         Ford

You could put a part number in A5 and use these formulae to get the connector in B5 and Speed in C5:

In B5:


In C5:


See VLOOKUP in the Excel Help files for a complete explanation.

Report •

Related Solutions

Ask Question