+ Reply to Thread
Results 1 to 7 of 7

Autogenerating Prices on Product List

  1. #1
    Registered User
    Join Date
    12-10-2019
    Location
    United States
    MS-Off Ver
    Excel 2016
    Posts
    10

    Question Autogenerating Prices on Product List

    Column A: List of All Products set as a drop down using Data Validation.

    When I select a product from the Data Validation Drop down I would like for it to autogenerate the price of the product into the cell to the right.

    I typically use a nested IF function to do this but the product list is 61 items long and you can only go up to I believe 7 nested IF Functions.

    Does anybody have an idea on how you can do this?
    Last edited by ImStuckPeaseHelpMe; 12-10-2019 at 01:18 PM.

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Autogenerating Prices on Product List

    probably ought to set up a "table" like item x in one column and price $22.15 in the next column, then use a vlookup or index/match against it.
    beyond that uploading a sample workbook will go a long way to getting you the specific formula you'd need.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Registered User
    Join Date
    12-10-2019
    Location
    United States
    MS-Off Ver
    Excel 2016
    Posts
    10

    Re: Autogenerating Prices on Product List

    Quote Originally Posted by Sambo kid View Post
    probably ought to set up a "table" like item x in one column and price $22.15 in the next column, then use a vlookup or index/match against it.
    beyond that uploading a sample workbook will go a long way to getting you the specific formula you'd need.
    I've attached the file. I like the data validation drop down list but am unsure how to attach the prices to the cell next to it based off which product is chosen in column F.

  4. #4
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Autogenerating Prices on Product List

    this vlookup would be one way... =VLOOKUP(C5,Lists!$A$3:$B$6,2,FALSE) put in D5 and dragged down.
    if you want to avoid #N/A then add =IFERROR to it like this... =IFERROR(VLOOKUP(C5,Lists!$A$3:$B$6,2,FALSE),0) and that will put a zero in when no match or this...
    =IFERROR(VLOOKUP(C5,Lists!$A$3:$B$6,2,FALSE),"") and it will leave a blank when no match.

  5. #5
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Autogenerating Prices on Product List

    I see you posted you want it off column F. Mine is off column C. You can use the same formula but adjusted to use it off column F like ...
    =IFERROR(VLOOKUP(F5,NEW LIST IN LIST TAB,2,FALSE),0)
    but you'll have to make a list with the prices, probably in the list tab.

  6. #6
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Autogenerating Prices on Product List

    this for the dropdown in col F looks like it will work for you... =VLOOKUP(F5,Lists!$E$3:$F$62,2,FALSE)
    all you have to do is fill in the amounts in column F of the lists tab to get the numbers.

  7. #7
    Registered User
    Join Date
    12-10-2019
    Location
    United States
    MS-Off Ver
    Excel 2016
    Posts
    10

    Re: Autogenerating Prices on Product List

    Thank you Sambo!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Autofilter VBA but also show product prices from other shop
    By rksingh2020 in forum Excel General
    Replies: 3
    Last Post: 01-06-2017, 12:26 AM
  2. Pivot Table Product Prices
    By dw_22801 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 08-18-2016, 01:14 PM
  3. Find forecast on the prices of a product
    By nageshpolu in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-16-2013, 11:59 AM
  4. Using IF/WHEN formula for determining product shipping prices
    By Batman777 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-13-2013, 12:34 AM
  5. create a "live" input table for product prices from a list of automatic unique values
    By andymcnichol in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 09-11-2012, 06:13 AM
  6. Update Prices on price list and ID and add New Items - Multiple Product Lines
    By KNS Brakes in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-26-2011, 09:50 AM
  7. Use a multiplier to change List Prices to Net prices
    By Dangada in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 09-06-2005, 12:05 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1