Trying to create a simple price list from a table is driving me nuts...help needed please!

[dazzys]

Hi All,

I have a problem. I'm no Excel expert which is why I've come here for some help. I have a price list, a snapshot is below, which is divided into three columns; Brand, Product, Price. What I'm trying to achieve, and so far failing abysmally, is the ability through a drop down list to select a Brand in a cell and the adjacent cell provides me a list of products associated with that Brand. I then want to be able to select a product and the next cell updates with the corresponding price. From my 'playing around' I've managed to create a drop down list for the Brand but duplicates are appearing which I don't want. That's as far as I've got.

Can anyone steer me in the right direction?

Brand Product Price
3D FURY 3D FURY HDMI-COMP £325.00
ADA SUITE 8300 £3,376.00
ALDOUS CAT6/JACK GREY £4.58
ALDOUS HOME NET/WK CAB LRG £250.00
ALDOUS 1U/CAT5 12 WAY PATCH £37.50
ALDOUS 1U/20WAY F-PANEL £33.50
ALDOUS 4U/BLANK FULL LENGTH £20.00
ALDOUS 1U/12WAY F-PANEL £25.50
ALDOUS 1U/ADAPTER PLT 8WAY £19.75
ALDOUS 1U BRUSH PANEL £16.95
ALDOUS 1U/BLANK PANEL FULL £11.65
ALDOUS 1U/MODULAR PAT PANEL £22.50
ALDOUS HOME NET/WK CAB MED £205.37
ALLTRADE 2WAY 'F' SPLITTER £7.00
ALLTRADE 3WAY 'F' SPLITTER £12.00
ALLTRADE 4WAY 'F' SPLITTER £15.00

Many thanks in advance
Daz

 

[oldbrewer]

brand

product

price

col G

brand1

prod1

10

brand2

prod2

11

brand

brand2

brand3

prod3

12

brand1

prod4

13

Count of product

brand2

prod5

14

product

Total

brand3

prod6

15

row 7

prod11

1

14

#####

brand1

prod7

10

prod14

1

11

brand2

prod8

11

prod17

1

99

brand3

prod9

12

prod2

1

11

brand1

prod10

13

prod20

1

11

brand2

prod11

14

prod5

1

14

brand3

prod12

15

prod8

1

11

brand1

prod13

10

Grand Total

7

brand2

prod14

11

brand3

prod15

12

brand1

prod16

13

brand2

prod17

99

brand3

prod18

15

brand1

prod19

10

brand2

prod20

11

a simple pivot table to get the products

then in cell marked #####

=IF(OR(G7="Grand Total",G7=""),"",OFFSET($B$1,MATCH(G7,$B$2:$B$21,0),1))

<colgroup><col span="6"><col><col><col span="7"></colgroup><tbody>
</tbody>

 

[dazzys]

Thanks for the reply. Do you have a sample spreadsheet you can attach with this on as I'm confused.

 

[oldbrewer]

not allowed by forum rules - just copy my answer and paste it into your excel sheet - pivot tables - do you know them a bit ?

 

[dazzys]

not allowed by forum rules - just copy my answer and paste it into your excel sheet - pivot tables - do you know them a bit ?

Not used pivot tables before but I've heard of them. I've copied your text into a new spreadsheet. awaiting further instructions.

 

[oldbrewer]

highlight A1 to C21
click data
select pivot table and pivot chart report
where is data = microsoft excel list or database
where is date
it will say A1 to C21
click layout
drag brand to the page box
drag product to the row box
drag product (again) to the data box
set to count of product
ok
existing worksheet
click cell G3
finish
select a brand in the top drop down
ok