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

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

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

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

Please find attached book1 and book2 to see the dilema

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"

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

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