+ Reply to Thread
Results 1 to 9 of 9

Excel VBA: Dropdown box selection in first sheet to populate 2nd sheet from 3rd sheet data

  1. #1
    Registered User
    Join Date
    04-26-2012
    Location
    Glasgow
    MS-Off Ver
    Excel 2010
    Posts
    8

    Unhappy Excel VBA: Dropdown box selection in first sheet to populate 2nd sheet from 3rd sheet data

    I need some VBA help in Excel - I have been using IF commands but now outgrown them!

    Based on my 'sheet 1' selection from a drop down list, cell D5 in 'sheet 2' will be populated by data from 'sheet 3'.

    Here is what I tried to do using an IF command however it exceeds the maximum number of nested commands. This worked fine before the limit was exceeded.

    (IF(Overview!$AQ$17=1,'Excel 10'!D5,(IF(Overview!$AQ$17=2,'133-10'!D5,(IF(Overview!$AQ$17=3,'E-3120'!D5,(IF(Overview!$AQ$17=4,'NW100'!D5,(IF(Overview!$AQ$17=5,'V-27'!D5,(IF(Overview!$AQ$17=6,'GEV MP C 250 (30m)'!D5,(IF(Overview!$AQ$17=7,'GEV MP C 250 (32m)'!D5,(IF(Overview!$AQ$17=8,'GEV MP C 275 (30m)'!D5,(IF(Overview!$AQ$17=9,'GEV MP C 275 (32m)'!D5,(IF(Overview!$AQ$17=10,'EV-47'!D5,(IF(Overview!$AQ$17=11,'G52-500 (52m)'!D5,(IF(Overview!$AQ$17=12,'G52-500 (58m)'!D5,(IF(Overview!$AQ$17=13,'G52-850 (52m)'!D5,(IF(Overview!$AQ$17=14,'G52-850 (58m)'!D5,(IF(Overview!$AQ$17=15,'AW 70 1500'!D5,(IF(Overview!$AQ$17=16,'AW 77 1500'!D5,(IF(Overview!$AQ$17=17,AW 82 1500'!D5,0)))))))))))))))))

    Would there also be the ability to automatically add a new product to the code if more were to be tagged on? With the IF commands I had to manually go in and add the extra data. I assume so if it is a lookup or a match function of some sorts.

    Any help greatly appreciated!

  2. #2
    Registered User
    Join Date
    04-26-2012
    Location
    Glasgow
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Excel VBA: Dropdown box selection in first sheet to populate 2nd sheet from 3rd sheet

    FYI the products in the IF nested commands I renamed to just numbers to try to keep the formula as simple looking as I could. Here is a list of what numbers go with what products...

    1 Excel 10
    2 133-10
    3 E-3120
    4 NW100
    5 V-27
    6 GEV MP C 250 (30m)
    7 GEV MP C 250 (32m)
    8 GEV MP C 275 (30m)
    9 GEV MP C 275 (32m)
    10 V-47
    11 G52-500 (52m)
    12 G58-500 (58m)
    13 G52-850 (52m)
    14 G52-850 (58m)
    15 AW 70/1500
    16 AW 77/1500
    17 AW 82/1500

  3. #3
    Valued Forum Contributor
    Join Date
    05-08-2012
    Location
    Georgia, USA
    MS-Off Ver
    Excel 2003, 2010
    Posts
    811

    Re: Excel VBA: Dropdown box selection in first sheet to populate 2nd sheet from 3rd sheet

    =The IF(Overview!$AQ$17=1, IF(Overview!$AQ$17=2, etc can be replaced by the function CHOOSE

    =Choose(Index_No, Condition1,Condition2, Condition3, ...Condition17)

    The function would look like this:
    =CHOOSE(Overview!$AQ$17,'Excel 10'!D5,'133-10'!D5,'E-3120'!D5 and so on up to condition 17

    VLOOKUP and INDEX and MATCH would also work
    ================
    Based on your data VLOOKUP and INDIRECT would also work
    Last edited by K m; 09-07-2012 at 08:22 AM.
    Click on star (*) below if this helps

  4. #4
    Registered User
    Join Date
    04-26-2012
    Location
    Glasgow
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Excel VBA: Dropdown box selection in first sheet to populate 2nd sheet from 3rd sheet

    Hello and thanks - tried it but it doesn't seem to work for some reason - it opens a window looking for a file about 6/7 times. When I do select the file (that I am working in) it eventually has #REF as the output on a couple of products.

  5. #5
    Registered User
    Join Date
    04-26-2012
    Location
    Glasgow
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Excel VBA: Dropdown box selection in first sheet to populate 2nd sheet from 3rd sheet

    Found my problem - it was my naming, unhid all the sheets and checked them, However....

    I now get #VALUE as a result.

    At least the window looking for a file has gone now!

  6. #6
    Valued Forum Contributor
    Join Date
    05-08-2012
    Location
    Georgia, USA
    MS-Off Ver
    Excel 2003, 2010
    Posts
    811

    Re: Excel VBA: Dropdown box selection in first sheet to populate 2nd sheet from 3rd sheet

    If you entered the formula correctly the CHOOSE function will work. I suspect it is a data entry problem

  7. #7
    Registered User
    Join Date
    04-26-2012
    Location
    Glasgow
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Excel VBA: Dropdown box selection in first sheet to populate 2nd sheet from 3rd sheet

    Here is what I am using and getting #VALUE from...

    =CHOOSE(Overview!$C$17,'Excel 10'!D5,'133-11'!D5,'E-3120'!D5, NW100!D5, 'V-27'!D5,'GEV MP C 250 (30m)'!D5, 'GEV MP C 250 (32m)'!D5, 'GEV MP C 275 (30m)'!D5, 'GEV MP C 275 (32m)'!D5, 'V-47'!D5, 'G52-500'!D5, 'G58-500'!D5, 'G52-850'!D5, 'G58-850'!D5, 'AW 70 1500'!D5, 'AW 77 1500'!D5, 'AW 82 1500'!D5)

    $C$17 is the dropdown box location, the simplification to a numbered system is in cell $AQ$17. I have tried with both.

  8. #8
    Registered User
    Join Date
    04-26-2012
    Location
    Glasgow
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Excel VBA: Dropdown box selection in first sheet to populate 2nd sheet from 3rd sheet

    Got it working - it was indeed my bad. Thank you so much for your help :D

  9. #9
    Valued Forum Contributor
    Join Date
    05-08-2012
    Location
    Georgia, USA
    MS-Off Ver
    Excel 2003, 2010
    Posts
    811

    Re: Excel VBA: Dropdown box selection in first sheet to populate 2nd sheet from 3rd sheet

    Mark this thread as [SOLVED] and click on the star at the bottom of this message to indicate that this was helpful

+ 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