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

D

dazzys

New Member
Joined
Aug 10, 2018
Messages
3
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
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
[TABLE="width: 998"]
<colgroup><col span="6"><col><col><col span="7"></colgroup><tbody>[TR]
[TD]brand[/TD]
[TD]product[/TD]
[TD]price[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]col G[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]brand1[/TD]
[TD]prod1[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]brand2[/TD]
[TD]prod2[/TD]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]brand[/TD]
[TD]brand2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]brand3[/TD]
[TD]prod3[/TD]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]brand1[/TD]
[TD]prod4[/TD]
[TD="align: right"]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Count of product[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]brand2[/TD]
[TD]prod5[/TD]
[TD="align: right"]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]product[/TD]
[TD]Total[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]brand3[/TD]
[TD]prod6[/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD][/TD]
[TD]row 7[/TD]
[TD]prod11[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]14[/TD]
[TD]#####[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]brand1[/TD]
[TD]prod7[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]prod14[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]brand2[/TD]
[TD]prod8[/TD]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]prod17[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]99[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]brand3[/TD]
[TD]prod9[/TD]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]prod2[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]brand1[/TD]
[TD]prod10[/TD]
[TD="align: right"]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]prod20[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]brand2[/TD]
[TD]prod11[/TD]
[TD="align: right"]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]prod5[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]brand3[/TD]
[TD]prod12[/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]prod8[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]brand1[/TD]
[TD]prod13[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Grand Total[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]brand2[/TD]
[TD]prod14[/TD]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]brand3[/TD]
[TD]prod15[/TD]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]brand1[/TD]
[TD]prod16[/TD]
[TD="align: right"]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]brand2[/TD]
[TD]prod17[/TD]
[TD="align: right"]99[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]brand3[/TD]
[TD]prod18[/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]brand1[/TD]
[TD]prod19[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]brand2[/TD]
[TD]prod20[/TD]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]a simple pivot table to get the products[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]then in cell marked #####[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 7"]=IF(OR(G7="Grand Total",G7=""),"",OFFSET($B$1,MATCH(G7,$B$2:$B$21,0),1))[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Thanks for the reply. Do you have a sample spreadsheet you can attach with this on as I'm confused.
 
Upvote 0
not allowed by forum rules - just copy my answer and paste it into your excel sheet - pivot tables - do you know them a bit ?
 
Upvote 0
oldbrewer said:
not allowed by forum rules - just copy my answer and paste it into your excel sheet - pivot tables - do you know them a bit ?
Click to expand...

Not used pivot tables before but I've heard of them. I've copied your text into a new spreadsheet. awaiting further instructions.
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,234
Messages
6,170,891
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top