Lists of Components

[Confused_UK]

Hi all

I am trying to build a spreadsheet to determine the cost of building a bespoke PC.

Column A of Sheet 1 has the schedule of the types of components that need to be present, motherboard, hard drive, memory, etc.

Sheet 2 has the individual components, Column A details the type of component (selected via a dropdown list from Column A of Sheet 1), Column B has the description, Column C details the cost and Column D details the selling price.

Is it possible to have a dynamic filter that can show the various motherboards wherever they are listed on Sheet 2 as a selectable list in Column B of Sheet 1, the same for the hard drive, memory, etc?

Any help would be appreciated

Thanks

 

[oldbrewer]

if sheet2 has a dropdown then only say motherbords will be listed

is there a third sheet with a list of components in random order ?

 

[Confused_UK]

At the moment, there are only two sheets.

Sheet 1

Sheet 2

The list of component types in Column A of Sheet 1 controls Column A in Sheet 2.

On Sheet 1, Column B for "Graphics Card", I am hoping to have a list of the individual components in rows 2,3,4,9 and 10 and any other instance where Column A equals "Graphics Card".

Does that help?

 

[oldbrewer]

component

pci adaptor

psi5

psi4

pci2

pci1

pci3

this column is the row

#NUM!

number of matching

#NUM!

compopnents

#NUM!

if the num's offend

#NUM!

they can be

#NUM!

error trapped out

descr.

pci adaptor

psi5

12

graphics card

gc2

sound card

sc2

this column

power supply

ps2

uses offset match

graphics card

gc1

to pull the descriptions

power supply

ps1

pci adaptor

psi4

18

sound card

sc4

sound card

sc3

I am still not clear

power supply

ps4

what you want though…

memory

m1

power supply

ps3

sound card

sc1

memory

m4

graphics card

gc3

memory

m2

memory

m5

pci adaptor

pci2

29

pci adaptor

pci1

30

pci adaptor

pci3

31

memory

m3

<colgroup><col><col><col span="5"><col><col span="5"></colgroup><tbody>
</tbody>

 

[Confused_UK]

Rather than having named ranges for each component as shown and linking that to each component type, is it possible to have a dynamic list that will automatically add all of the instances of that component type that appear on Sheet2, irrespective of where it occurs? That list would then only show the available items of say Processors.

 

[oldbrewer]

surely that is what I just did for you - it found every instance regardless of order...