Results 1 to 7 of 7

Thread: Dynamic Drop down working from dynamic source list

  1. #1

    Dynamic Drop down working from dynamic source list



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi good people!,

    I have a teenie problem. On the attachment you will see column B is dependent on column A. In my actual workbook column B is dependent on another drop down. So, depending on the drop down selection, column B will populate with different values, and will also have either more or less items. Okay, so for this example we make changes to column A. So whether I delete some items in column A or change some of them to "0"'s, I need the drop down in column D to be dynamic. Please help me with this. All and any help will be greatly appreciated!
    Attached Files Attached Files

  2. #2
    Conjurer sambit's Avatar
    Join Date
    Sep 2014
    Posts
    143
    Articles
    0
    Excel Version
    Excel2007
    Hi,
    By using helper coloum it may be possible.

    Try below array formula (Ctrl+Shift+Enter).
    =IFERROR(INDEX($C$3:$C$8,MATCH(ROWS($C$3:C3),COUNTIF($C$3:$C$8,"<="&$C$3:$C$8),0)),"")

    plz see attachment. is this help you ?
    Attached Files Attached Files

  3. #3
    Hi Sambit,

    This is only adjusting the final column. This I have in place already. I need the actual drop down to also adjust so that no blanks are present. Also I cannot open your attachment as your file is a PHP type?

  4. #4
    Conjurer sambit's Avatar
    Join Date
    Sep 2014
    Posts
    143
    Articles
    0
    Excel Version
    Excel2007
    Hi,
    plz see the attachment.
    Attached Files Attached Files

  5. #5
    Hi Sambit,

    for some reason your attachments come through as PHP type, so I cannot open them. Anyways, I have finally sorted this out. What I did was use the ="A1:A"&Countif(A:A,">""") in B1. I then created a named range "Asset", and pointed it to B1 using "=Indirect(sheet1!B1)". I then changed the source of the drop down to "= Asset". The drop down now adjusts according to how the source list changes.

    Thank you kindly for being out there. It is really appreciated.

  6. #6
    Conjurer sambit's Avatar
    Join Date
    Sep 2014
    Posts
    143
    Articles
    0
    Excel Version
    Excel2007
    Hi Juriemagic,
    The above excel file is open in my PC. However, plz go through step by step process as follows:

    suppose your data start in coloum B from row 2 to row 7 like A,C,B,Q,G,H.

    First step: we make helper coloum i.e, Coloum A, in cell A2 put below formula
    =IF(B2="","",MAX($A$1:A1)+1)

    copied down to cell A7.

    Second step: we make another helper coloum i.e, Coloum H, put below array (Ctrl+Shift+Enter)formula in cell H2
    =IFERROR(INDEX($B$2:$B$7,MATCH(ROWS($B$2:B2),COUNTIF($B$2:$B$7,"<="&$B$2:$B$7),0)),"")

    copied down up to cell I7

    Third step : we have to make name list having name data and put formula
    =OFFSET(Sheet2!$H$1,1,0,MAX(Sheet2!$A:$A),1)

    Fourth Step: create Data validation and in source put below formula
    =Data

    Now check in data validation list when you delete any data in orginal coloum B.
    Attached Files Attached Files

  7. #7
    Hi Sambit,

    thank you very much for this answer. Your time and efforts are greatly appreciated!.. have a blessed day!

Posting Permissions

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