+ Reply to Thread
Results 1 to 9 of 9

Dynamic range with indirect in data validation list doesn't work

  1. #1
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Dynamic range with indirect in data validation list doesn't work

    Hello everybody

    I have two columns A & B with Headers : Header1 is "Products" and below this header there are data related to it ..
    In Column B there is the header "Sales" and below this header there are data related to it ..

    I have created a fixed named range "MyList" to include both headers .. Products and Sales
    Also i have created two dynamic ranges "Products" and "Sales"

    In F4 cell I have created drop down list of "MyList" and in cell F5 I put this formula
    Please Login or Register  to view this content.
    But it seems not to work with dynmaic ranges
    I need UDF function that can be used in the source field that converts the dynamic range to fixed ...
    < ----- Please click the little star * next to add reputation if my post helps you
    Visit Forum : From Here

  2. #2
    Forum Expert
    Join Date
    02-22-2013
    Location
    London, UK
    MS-Off Ver
    Office 365
    Posts
    1,218

    Re: Dynamic range with indirect in data validation list doesn't work

    Could you perhaps upload a workbook sample so we can better understand what you are trying to achieve? What's the purpose of cell F5 wth the formula provided?

  3. #3
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Dynamic range with indirect in data validation list doesn't work

    Hello Mr. Berlan
    Here's an attachment
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Dynamic range with indirect in data validation list doesn't work

    Any help about this topic?

  5. #5
    Forum Expert
    Join Date
    02-22-2013
    Location
    London, UK
    MS-Off Ver
    Office 365
    Posts
    1,218

    Re: Dynamic range with indirect in data validation list doesn't work

    Maybe (in the Sheet module):
    Please Login or Register  to view this content.
    Last edited by berlan; 06-29-2015 at 02:01 PM.

  6. #6
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Dynamic range with indirect in data validation list doesn't work

    Thanks Mr. Berlan
    I imagine that there may be UDF function that converts dynamic ranges to fixed ranges temporarily
    Thanks for help

  7. #7
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Dynamic range with indirect in data validation list doesn't work

    Hi,

    This is using UDF method as your requirement :

    Code in standard module :
    Please Login or Register  to view this content.

    then insert a new name range object : Selector
    with its content is this formula :
    Formula: copy to clipboard
    Please Login or Register  to view this content.



    and finally change the validation of cell F5 to this :
    Formula: copy to clipboard
    Please Login or Register  to view this content.




    Regards
    Attached Files Attached Files

  8. #8
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Dynamic range with indirect in data validation list doesn't work

    Mr. Karedog the Great Friend
    Thank you very much for this special solution
    I have searched yesterday for more than three hours and I didn't find such a solution .. It is very wonderful
    Thanks alot

  9. #9
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Dynamic range with indirect in data validation list doesn't work

    You are welcome, thanks for the reps also.
    I think my body is not that big (Great)


    Regards

+ 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. HI, Everyone, Desperate that Indirect function doesn't work with dynamic range
    By damiending in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-08-2015, 12:27 AM
  2. Replies: 9
    Last Post: 04-01-2015, 04:23 PM
  3. Data Validation - How to work with 2 Dynamic Lists where One list have Repetitive values.
    By Vinod Krishna.C in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-03-2014, 01:19 PM
  4. Replies: 1
    Last Post: 03-01-2013, 06:59 PM
  5. [SOLVED] Dynamic range from every nth value in a column in a data validation list
    By Xaoc in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-08-2013, 08:47 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