+ Reply to Thread
Results 1 to 7 of 7

Autopopulate using a dropdown menu

  1. #1
    Forum Contributor
    Join Date
    06-17-2010
    Location
    Philadelphia, PA
    MS-Off Ver
    Excel 2010
    Posts
    228

    Autopopulate using a dropdown menu

    I have 3 choices that I would like to put in a dropdown menu: Voucher 1, Voucher 2, and Voucher 3. I would like to be able to make it so that I can choose Voucher 1 from a dropdown and then 2 rows under the dropdown Voucher 1.

    Also, The 3 vouchers are currently saved into one spreadsheet with 5 rows seperating each one. If I put the title of each voucher in a cell above the voucher, how would I take each cell and put that into the dropdown?

    Thank you for your help!

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: Autopopulate using a dropdown menu

    For Part 2 of your question, the data validation list must be from sequential cells. You could, for example, in J1:J3 put = A2, =A24, =A50 and then use J1:J3 for your data validation.

    For part 1, take a look at what I did in the attachment. Choose from the dropdown in A1 and the cells from the appropriate voucher (on sheet2) automatically fill. I'll be back tomorrow if you have questions (if that's what you are looking for).
    Attached Files Attached Files
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Forum Contributor
    Join Date
    06-17-2010
    Location
    Philadelphia, PA
    MS-Off Ver
    Excel 2010
    Posts
    228

    Re: Autopopulate using a dropdown menu

    Chemist - this is exactly what I'd like to do, but I'm not sure what you did to make it work... if you could explain a little further I would appreciate it.

    Thanks!

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: Autopopulate using a dropdown menu

    Okay, in A1, I have the dropdown list of vouchers using data validation. Let me know if you don't know how to do that (or any of the following steps).

    On sheet 2 are all of your vouchers. First name them. Select the range and in the name box (where you normally see the cell address, i.e. A1), type in voucher1, enter.
    Names should be the same as the list in your dropdown box minus spaces. Names can't have spaces.

    Going back to Sheet1, select an area equal to the range size of your largest voucher (ideally, make all your vouchers the same size). Type in
    =IF(A1<>"",INDIRECT(SUBSTITUTE(A1," ","")),"") and instead of ENTER, use CNTRL SHFT ENTER. This enters your formula in all the selected range as an array. If you click on it, you'll see they have brackets around them. This formula tells Excel to take the value in A1 and remove the spaces. Then, the INDIRECT tells Excel, not to treat this as text but as a reference, the reference being your named range. Because it's entered as an array, it puts the each relative cell on sheet2 into the appropriate cells on sheet1.

    If your vouchers have blank cells in them, the formula will return a 0. To avoid this, instead of leaving those cells blank on sheet2, enter =""

    Any questions?
    Last edited by ChemistB; 02-16-2011 at 10:05 AM.

  5. #5
    Forum Contributor
    Join Date
    06-17-2010
    Location
    Philadelphia, PA
    MS-Off Ver
    Excel 2010
    Posts
    228

    Re: Autopopulate using a dropdown menu

    Hi Chemist - so far it seems to be working. Thank you very much. Is it possible to keep the cell formatting for the original vouchers? (All the vouchers have the same formatting)

  6. #6
    Forum Contributor
    Join Date
    06-17-2010
    Location
    Philadelphia, PA
    MS-Off Ver
    Excel 2010
    Posts
    228

    Re: Autopopulate using a dropdown menu

    Here is the template I'm working with. The cells that have "X" in them will have information in them.
    Attached Files Attached Files

  7. #7
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: Autopopulate using a dropdown menu

    Formulas cannot carry over formats. If they all have the same formatting, then you should format the cells on Sheet1 to duplicate the formatting on sheet2. If it's more complicated than that, you may need a VBA solution and should post in the Excel Programming folder. Hope that helps.

+ 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