Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

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

  1. #1
    New Member
    Join Date
    Aug 2018
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  2. #2
    Board Regular
    Join Date
    Apr 2010
    Posts
    10,429
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

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

    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))

  3. #3
    New Member
    Join Date
    Aug 2018
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

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

  4. #4
    Board Regular
    Join Date
    Apr 2010
    Posts
    10,429
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

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

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

  5. #5
    New Member
    Join Date
    Aug 2018
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by oldbrewer View Post
    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.

  6. #6
    Board Regular
    Join Date
    Apr 2010
    Posts
    10,429
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

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

    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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •