system builder

[gumby]

I want to design a sheet that will allow me to select components, manufacturers for a P.C.(from a validate list)and automatically select the correct price. I've tried vlookup but couldn't get it to work. The sheet will look something like this.

cell a3-20 = component(cpu,ram,printers,etc)
cell b3 = manufacturers(vlookup?)
cell c3 = model(vlookup?)
cell c4 = price

this data is is a separate sheet in the following format....

Column A
manufacturer
component(type of board P4 or AMD)
model

column B
prices
column A Column B
Chaintech Excel blank

P4 blank

9BIA0 intel 845 Price
9BJA0, Intel 845 Price
9BJD, Intel845 Price

Socket 370 (celeron) blank

6AJA4T-G100, VIA 694X price
6VJD2 VIA PRO 266

Socket A (athlon,Duron)

LAN/MODEM COMBO CARD/ACR SLOT
7AJA2,VIA 8363A
7VJDA, VIA KT266

VIA (RB) -- 2 year warranty

P4-478

ES676B
ASUS

P4-423

P4T, Skt 423
P4TM, SKT423

 

[Al Chara]

Let me see if I get this right...you want to be able to select a type of component, then select the manufacturer and model number, and have it display the correct price.

Now you have the spreadsheet setup as components in column A and the respective manufacturer in same row column B, model number in same row column C, and price in next row column C (the price placement doesn't make sense to me).

 

[gumby]

oops, sorry. I kind of messed it up(long day). It sould be

cell a3-20 = component(cpu,ram,printers,etc)
cell b3 = manufacturers(vlookup?)
cell c3 = model(vlookup?)
cell d3 = product
cell e3 = price

I tried to format my post to be easy to under stand, but the format changed when I posted it.

I'll try to explain it better.

column A

manufacturer-(chaintech excel, VIA (RB) -- 2 year warranty,asus)

Model-(P4,socket,socket 370,socket A,P4-478,etc...

Product-(9BIA0 intel 845, 6AJA4T-G100, VIA 694X,etc...

they are listed in this order in column A - manufacturer, model, product,manufacturer, model, product,manufacturer, model, product,etc...

Column B is the prices

hope this helps.

 

[Al Chara]

column A

manufacturer-(chaintech excel, VIA (RB) -- 2 year warranty,asus)

Model-(P4,socket,socket 370,socket A,P4-478,etc...

Product-(9BIA0 intel 845, 6AJA4T-G100, VIA 694X,etc...

they are listed in this order in column A - manufacturer, model, product,manufacturer, model, product,manufacturer, model, product,etc...

Column B is the prices

hope this helps.

Where is the reference to what type of component it is?

 

[gumby]

the data starts in cell A20 and prices in B20 and goes like this. B20&21 corespond with A20&21 they are categories and have no price data(the "/" represents spaces for clarity.

A20 - chaintech excel / B20 - blank
A21 - P4/////////////// B21 - blank
A22 - 9BIA0 intel 845// B22 - price
A23 - 6AJA4T-G100////// B23 - price
A24 - VIA 694X///////// B24 - price
A25 - socket 370/////// B25 - blank
A26 - 6AJA4T-G100////// B26 - price
A27 - 6VJD2 VIA PRO 266 B27 - price
A28 - socket A///////// B28 - blank
A29 - 7AJA2//////////// B29 - price
A30 - 7VJDA//////////// B30 - price

A20 is the manufacturer
A21,25,28 are the product type (labaled as "model" in my reply to your reply)
A22,23,24,26,27,29,30 are the actual products with their prices in the coresponding B cells.
A31 is another manufacturer
A32,36,39 product type
A33,34,35,37,38,40,41,42,43,44 are more actual products with their prices in the coresponding B cells.