+ Reply to Thread
Results 1 to 9 of 9

Using a dynamic named range from another workbook

  1. #1
    Registered User
    Join Date
    01-05-2014
    Location
    London, England
    MS-Off Ver
    Excel 20010
    Posts
    28

    Using a dynamic named range from another workbook

    I have a workbook called Start Permit.xlsm and one of the many worksheets is named Data.
    The Data worksheet is basically a central list of numerous persons names and dates.
    Cell A2 =IF(ISNUMBER(SEARCH(('[V6.XLSM]Create Permit'!$V$18,B2)),MAX($A$1:A1)+1,0) 'based on the text value of V18 a numerical list is produced in column A and each 'hit' gets a + 1 number from the previous row hit.
    Column B contains numerous names.
    Cell M2 = IFERROR(VLOOKUP(ROWS($M$2:M2),A2:B199,2,0),"") 'The result of this is displays persons names from the search of text from V18 from persons names column B
    Cell L1 =COUNTIF($M$2:M200,"?*")
    Cell L2 = OFFSET(M2,,,COUNTIF($M$2:M200,"?*))
    My named range Validation_List =OFFSET(M2,,,COUNTIF($M$2:M200,"?*))

    A second workbook called 'V6,xlsm' and one of the many worksheets is called Create Permit' my cell V18 has data validation List = MyValidation_List.
    In names manager MyValidation_List ='Start Permit.xlsm'!Validation_List

    The text from V6,xlsm Create Permit V18 does fully function within Start Permit.xlsm Data but my V18 data validation List = MyValidation_List does not result in a list of names following enter a text value like 'john' or any other character.

    I'm so close can someone help please

  2. #2
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2013
    Posts
    3,756

    Re: Using a dynamic named range from another workbook

    Hi there,

    See if the attached workbooks do what you need.

    The Named Range in your second (V6) workbook must be defined as a WorkBOOK-level Named Range. Data Validation dropdown lists for a cell can refer either to cells on the worksheet on which the cell itself is located, or else to cells contained in a Named Range defined at workbook level - Named Ranges defined on other worksheets can not be used.

    Both workbooks must be open.

    Hope this helps - please let me know how you get on.

    Regards,

    Greg M
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    01-05-2014
    Location
    London, England
    MS-Off Ver
    Excel 20010
    Posts
    28

    Re: Using a dynamic named range from another workbook

    The scope is set as Workbook for both named ranges. Your solution has lost its functionality or is having the same problems as mine. When I used fixed number of rows in a named range the formula works. But I am seeking to search for test in V18 (partial name) and the result which is visible in Start Permit is working.
    As a check I have used different text in V18 and checking the named range Validation_List is dynamic and L1 counts the length of the named range, so another solution is required. sorry

  4. #4
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2013
    Posts
    3,756

    Re: Using a dynamic named range from another workbook

    Hi again,

    Thanks for the prompt feedback.

    When I open both workbooks and click on Sheet1, Cell A1 of the V6 workbook, the data validation dropdown list which is displayed shows all of the names contained in the Named Range (MyValidation_List) defined at workbook-level in the Start Permit workbook. Is this happening at your end?

    Regards,

    Greg M

  5. #5
    Registered User
    Join Date
    01-05-2014
    Location
    London, England
    MS-Off Ver
    Excel 20010
    Posts
    28

    Re: Using a dynamic named range from another workbook

    Please find attached book1 and book2 to see the dilema
    Attached Files Attached Files

  6. #6
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2013
    Posts
    3,756

    Re: Using a dynamic named range from another workbook

    Hi again,

    In workbook Start Permit / Book 2, the Named Range containing the personnel names is defined using a mixture of relative and absolute addresses, i.e.

    Please Login or Register  to view this content.
    but you must use absolute references throughout, i.e.

    Please Login or Register  to view this content.

    Making this change enabled the workbooks you posted to work correctly at my end.


    Hope this helps - please let me know how you get on.

    Regards,

    Greg M
    Last edited by Greg M; 08-23-2015 at 07:55 PM. Reason: Minor addition

  7. #7
    Registered User
    Join Date
    01-05-2014
    Location
    London, England
    MS-Off Ver
    Excel 20010
    Posts
    28

    Re: Using a dynamic named range from another workbook

    Your reply threw me a bit "=OFFSET(Sheet1!$B$1,,,COUNTIF(Sheet1!$B$2:$M$199,"?*"))" why the reference to B1?
    I have saved your amended files book1 and book2 and the data validation list fails to work. "The source currently evaluates to an error"

  8. #8
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2013
    Posts
    3,756

    Re: Using a dynamic named range from another workbook

    Hi again,

    Open the Book2 you posted here, and select Cell A1 on Sheet1.

    Then open the Name Manager and select the Name "Validation_List".

    The RefersTo box will contain the reference =OFFSET(Sheet1!B1,,,COUNTIF(Sheet1!$M$2:B199,"?*"))

    Click anywhere inside the above text, and the range of personnel names (B1:B59) will be highlighted - this includes the header cell B1.

    HOWEVER - if you select any cell other than A1 and perform the above steps, the highlighted range will be different. This is why it is so important to use absolute references here. There are times when relative references must be used (e.g. defined name formulas), but this is not one of those times!

    In fact, the following is a better definition for the dynamic Named Range of personnel:

    Please Login or Register  to view this content.
    This will allow you include up to 100 personnel names in the range / dropdown list, and excludes the header cell.

    I'm attaching the latest versions of the workbooks.

    As always, please let me know how you get on.

    Regards,

    Greg M
    Attached Files Attached Files

  9. #9
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2013
    Posts
    3,756

    Re: Using a dynamic named range from another workbook

    Hi again,

    Many thanks for all of your feedback and for the private message.

    Feel free to shout if there's anything else you think I might be able to help you with.

    Best regards,

    Greg M

+ 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. [SOLVED] dynamic named range not populating combo box list if range = single cell
    By HeyInKy in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-24-2014, 05:27 PM
  2. Dynamic / Named Print Range To Include Another Range of Cells
    By thekrakenwakes in forum Excel General
    Replies: 0
    Last Post: 07-25-2014, 07:52 AM
  3. [SOLVED] creating a named range taking the avg. to date of a dynamic range.
    By siggisigg in forum Excel General
    Replies: 1
    Last Post: 07-15-2014, 08:58 AM
  4. [SOLVED] Dynamic Named Range to Auto-Populate another range based on criteria
    By BeachRock in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-24-2014, 06:46 PM
  5. [SOLVED] Dynamic Named Range Help - Range Based on Values in Column
    By Filibuster in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-03-2012, 11:13 AM
  6. Named ranges-Should I use a dynamic named range
    By foseco in forum Excel General
    Replies: 4
    Last Post: 06-11-2009, 03:56 PM
  7. getting the absolute range address from a dynamic named range
    By junoon in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-21-2006, 09:30 AM

Tags for this Thread

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