+ Reply to Thread
Results 1 to 28 of 28

Populate data from drop-down list selection

  1. #1
    Registered User
    Join Date
    05-18-2014
    MS-Off Ver
    Excel 2003
    Posts
    17

    Populate data from drop-down list selection

    Hello - I've been reading through a few posts to try and figure out how to solve my problem, but can't seem to get it right I've tried a combination of formulas using vlookup's, index and match to no avail...

    Anyway, I've attached a sample file. Essentially, I want to select a region and month on the 'Validation' tab from the two independent data validation lists and have the data (revenue) populate in cells C7-G7 for Products A - E for the respective region and time period. (The source tab is titled 'Data')

    Please let me know if I need to clarify anything - but I think it'll be pretty straight forward once you view the file. Really appreciate any and all help on this, thanks in advance!
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,067

    Re: Populate data from drop-down list selection

    You have some pretty glaring circular references in Data sheet!! (circ ref is when a cell is used within a calc that includes that cell)

    for instance in D5, downwards you have...
    =SUMIF($D$4:$D$365, $E5,D$4:D$365)
    meaning...
    in D5, put the sum of the range D4:D365 (which includes D5), based on what is in E5. Soooo D5 is part of the sum that goes INTO D5

    Column E is even worse...
    E5=SUMIF($D$4:$D$365, $E5,E$4:E$365)
    So E5 has to be the sum of items in D, based on what's IN E5

    Fix those 1st, then we can look at the rest
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,828

    Re: Populate data from drop-down list selection

    HI

    please re do your data sheet as told by "FDibbins", i have pasted those values and provided a solution, i am not sure whether it as per u r requirement or not.


    Punnam
    Attached Files Attached Files

  4. #4
    Valued Forum Contributor Miroslav R.'s Avatar
    Join Date
    05-16-2013
    Location
    NMnV, Slovakia
    MS-Off Ver
    Excel 2007
    Posts
    479

    Re: Populate data from drop-down list selection

    Hi there,

    Quote Originally Posted by FDibbins View Post
    You have some pretty glaring circular references in Data sheet!! (circ ref is when a cell is used within a calc that includes that cell)
    I freeze them all and You have to do the same if You want to get it to work with this formula (put in cell C7 in validation sheet and drag to right):

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Note that this is array formula and needs to be confirmed by Ctrl+Shift+Enter. Also it is designed to fixed range in Your Data sheet.

    Hope it helps
    Regards
    Miroslav R.

    (If You like my solutions, feel free to add reputation.)

  5. #5
    Registered User
    Join Date
    05-18-2014
    MS-Off Ver
    Excel 2003
    Posts
    17

    Re: Populate data from drop-down list selection

    Thank you all for replying!

    @Punnam - Yes, that's exactly how I want it to appear when a region and month is selected - so the values change and correspond to the selected region and month.

    @Miroslav - The formula is similar to one's I've seen in this scenario, however, I get an error message when testing. It highlights on D22 after the index function - any thoughts why? I followed your instructions and entered in cell C7


    Thanks again!
    MC

  6. #6
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,828

    Re: Populate data from drop-down list selection

    Hi MC,

    Was u r question answered Solved ?

    Punnam

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,067

    Re: Populate data from drop-down list selection

    Quote Originally Posted by Miroslav R. View Post
    Hi there,
    I freeze them all and You have to do the same if You want to get it to work with this formula (put in cell C7 in validation sheet and drag to right):
    I know how to use circ errors, but they are never a good idea to leave them in a workbbok - they often cause all sorts of wierd things to happen, and are only used for very specific situations...I do not believe this is 1 of them

  8. #8
    Registered User
    Join Date
    05-18-2014
    MS-Off Ver
    Excel 2003
    Posts
    17

    Re: Populate data from drop-down list selection

    Hi Punnam - No my question hasn't been solved yet ... I was confused on what FDibbins suggested to do with re-working the Data set; not sure exactly how to fix.

  9. #9
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,828

    Re: Populate data from drop-down list selection

    Hi MC,

    So u are trying to fetch data which is under circular reference ?

    Punnam

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,067

    Re: Populate data from drop-down list selection

    what exactly are you trying to do in Data sheet?

  11. #11
    Registered User
    Join Date
    05-18-2014
    MS-Off Ver
    Excel 2003
    Posts
    17

    Re: Populate data from drop-down list selection

    @ Punnam - yes i'm trying to include the data under circular reference, but I'm trying to reformat to not have a circ ref error. I'm not really concerned with how the data sheet appears as it will be hidden, my struggle is setting up a working formula that calls in the data via the drop down selections. Should I recreate the data set? Thanks again!

  12. #12
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,828

    Re: Populate data from drop-down list selection

    HI MC,

    Yea you need to make it free from circular references as told by FDibbind, they will throw errors very often

    Punnam

  13. #13
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,067

    Re: Populate data from drop-down list selection

    Did you see post # 10?

  14. #14
    Registered User
    Join Date
    05-18-2014
    MS-Off Ver
    Excel 2003
    Posts
    17

    Re: Populate data from drop-down list selection

    Hi @ FDibbins, with the Data Sheet, I'm simply trying to group revenue by Products A-E by month. It doesn't have to stay in this exact format - I laid it out as such as I thought it would be simple to retrieve via a vlookup or index function. How should I rearrange the data file to avoid circ ref errors? Sorry if I'm not communicating my problem properly... I appreciate your help and patience!

  15. #15
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,067

    Re: Populate data from drop-down list selection

    Where is the info in Data sheet coming from?

  16. #16
    Registered User
    Join Date
    05-18-2014
    MS-Off Ver
    Excel 2003
    Posts
    17

    Re: Populate data from drop-down list selection

    I pull it in from a report builder client in excel. The data is confidential, so I kept it generic

  17. #17
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,828

    Re: Populate data from drop-down list selection

    Hi MC,

    To remove circular references using Error Checking-Circular reference.

    Punnam

  18. #18
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,067

    Re: Populate data from drop-down list selection

    OK, put some more realist (but still non-sensitive) data into that table, so we can see what you are really working with. Those formulas you have in those columns are a complete mess, and dont really mean - or do - anything

  19. #19
    Registered User
    Join Date
    05-18-2014
    MS-Off Ver
    Excel 2003
    Posts
    17

    Re: Populate data from drop-down list selection

    OOOOK, I see the problem now!! I looked at the original file I uploaded and saw the formulas you are referring to; those are a copy/paste error - that's why I was so confused with your original post, FDibbins. All cells on the data file should just be raw data, no formulas. I'm reattaching the file with the formulas removed. Does that help?
    Attached Files Attached Files

  20. #20
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,828

    Re: Populate data from drop-down list selection

    Hi MC,

    Is anything else is i have forgot to link up guide me.

    Punnam
    Attached Files Attached Files

  21. #21
    Registered User
    Join Date
    05-18-2014
    MS-Off Ver
    Excel 2003
    Posts
    17

    Re: Populate data from drop-down list selection

    Thanks, Punnam - you have it linked properly , now is there a way to link to the drop down lists?

  22. #22
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,828

    Re: Populate data from drop-down list selection

    Hi Mc,

    So you don't want to have data validation?
    am i correct ?

    Punnam

  23. #23
    Registered User
    Join Date
    05-18-2014
    MS-Off Ver
    Excel 2003
    Posts
    17

    Re: Populate data from drop-down list selection

    I want the data to populate in the appropriate cells based on the drop down list selection, so if I select France amd the month of April from the lists, I want the corresponding values in the Data tab to populate in the cells for all products, make sense?

  24. #24
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,828

    Re: Populate data from drop-down list selection

    Plz check the cells which are colored with Yellow, it is having a drop down list as u requested

  25. #25
    Registered User
    Join Date
    05-18-2014
    MS-Off Ver
    Excel 2003
    Posts
    17

    Re: Populate data from drop-down list selection

    Brilliant! Exactly what I need. Thanks so much

  26. #26
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,828

    Re: Populate data from drop-down list selection

    Hi MC,

    1) Can you let us know the post 25 is referring to whom?
    2) was your question answered and solved ?

    Punnam

  27. #27
    Registered User
    Join Date
    05-18-2014
    MS-Off Ver
    Excel 2003
    Posts
    17

    Re: Populate data from drop-down list selection

    Well thanks everyone for chiming in to help, but #25 was directed at you, Punnam. Yes, question was answered and solved.

  28. #28
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,828

    Re: Populate data from drop-down list selection

    Hi MC,

    welcome, so take some time and mark the thread solved in tools and rate any one whom you feel have helped you in solving .

    Punnam

+ 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. Populate data in cell based on drop down list selection
    By dwoodson297 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-14-2013, 02:07 PM
  2. Populate Drop-down list based on selection from previous drop-down list
    By Diventus in forum Excel - New Users/Basics
    Replies: 14
    Last Post: 01-11-2013, 10:55 AM
  3. [SOLVED] From drop down list selection populate multiple Cells
    By logcabin123 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-21-2012, 12:43 PM
  4. Populate Drop-down list based on selection from previous drop-down list
    By poison_stone in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 12-11-2012, 06:10 PM
  5. Drop Down/Selection List to Populate a Graph
    By Swoootie in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-20-2012, 05:42 AM

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