+ Reply to Thread
Results 1 to 6 of 6

Populate Data (Based on Drop Down Selection)

  1. #1
    Registered User
    Join Date
    07-01-2010
    Location
    United States, Kansas
    MS-Off Ver
    Excel 2007
    Posts
    3

    Populate Data (Based on Drop Down Selection)

    Can anyone assist in helping me with the following? I have a dropdown box and when a user selects an account from this box i would like the data from the orange box (another spreadsheete consisting of account forecasts & shipments for a particular product) to populate in the yellow fields for the product (depending on the account selection). Hopefully this make sense, not sure what type of formula i should use, thoughts? Appreciate any help...
    Attached Files Attached Files

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Populate Data (Based on Drop Down Selection)

    If you are using the combo box, change the cell link to a single cell, like $I$3

    then use formula:

    =IF($I$3=1,"",SUMIFS(K$4:K$8,$I$4:$I$8,INDEX($S$2:$S$4,$I$3-1),$J$4:$J$8,$A5))

    or, if you are using it in 2003 or earlier also....

    =IF($I$3=1,"",SUMPRODUCT(--($I$4:$I$8=INDEX($S$2:$S$4,$I$3-1)),--($J$4:$J$8=$A5),K$4:K$8))
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    07-01-2010
    Location
    United States, Kansas
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Populate Data (Based on Drop Down Selection)

    Thanks for the response, why would i only select a single cell? Can you by chance clarify by looking at the excel file i attached...thanks again for your help, its appreciated!!

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Populate Data (Based on Drop Down Selection)

    The cell link is the cell you want to send a numeric result that represents the position within the list that your choice is in... this way you can use that result to get do your search and compare.

  5. #5
    Valued Forum Contributor ron2k_1's Avatar
    Join Date
    09-30-2009
    Location
    Not the USA
    MS-Off Ver
    Excel 2003, 2007
    Posts
    606

    Re: Populate Data (Based on Drop Down Selection)

    Quote Originally Posted by jkoste03 View Post
    Thanks for the response, why would i only select a single cell? Can you by chance clarify by looking at the excel file i attached...thanks again for your help, its appreciated!!
    In trying to ease down the load of your contributor. Let answer, the control cell link only needs to be once cell. In that cell it will return the respective position of your selection. For instance, I would change your input range to just $S$2:$S$4 rather than $S$1:$S$4 under the Format Control window. Therefore, if you choose Account C, that is on relative position 3, and it will return that on the assigned cell link.

    I am not quite sure whether it matters to have a range, as I believe, that it will only modify your first cell in case you have specified a cell range. But to be on the safe side and to avoid possible issues, its best you limit it to just one cell.
    Ron
    Knowledge is knowing that a tomato and pepper are fruits. Wisdom is knowing whether to put these in a fruit salad

    Kindly

    [1] Use code tags. Place "[code]" before the first line of code and "[/code"]" after the last line of code. Exclude quotation marks
    [2] Mark your post [SOLVED] if it has been answered satisfactorily by editing your original post in advanced mode.
    [3] Thank (using the little scale) those that provided useful help; its nice and its very well appreciated

  6. #6
    Registered User
    Join Date
    07-01-2010
    Location
    United States, Kansas
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Populate Data (Based on Drop Down Selection)

    Thanks for everyones help, will keep you posted if i don't figure it out.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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