On excel, I've two tabs: one listing all people participating to an event, with a lot of details. I've a "FullName" column which is basically defined by:

=[@LastName]&" "&[@FirstName]

This column is named "FullName"(Original ;) )

In the other tab, I want to put the planning of the people and being able to select the person here, based on the previously mentionned column.

I tried to create a dynamic named range, but the issue is that the list of people could be like:

"FullName one"
"Fullname two"
" "
"Fullname four"

and with all solution I currently found, they don't take in account that " " should be considered as blank for my case, and that maybe there is an empty cell, then a non-empty cell.

How would you handle this? I guess that dynamic named range are the good approach, but what formula?


You may want to select the data of the Full Name column of your table, then apply a named range, e.g. Full_Name on it. Voi'la, your reference is expanding with your table.



  1. You insert a Pivot referencing your table. Place the "Full Name" tag to the visible tags. Place it to the first row for convenience.
  2. You set a hella lot of things in your Pivot settings: sort in ascending order, hide column grand, set the missing item limit to none, I think there's a caption hide too (hides top left system inserted text).
  3. You add a name to the column of your pivot (e.g. Your_List).
  4. You add a validation reference to Your_List.
  5. Refreshing goes with Ctrl+Alt+F5

For the hardcore version of reference use validation


(only your pivot's values will appear). If you don't choose #1's convenient variant it won't work. You can set your pivot to refresh on opening the workbook.

  • Yeah, but it is containing the empty fields – J4N Apr 9 '15 at 9:17
  • Why do you have empty fields [sic] in your primary key? What other data does the source table contain? – sicarius92 Apr 14 '15 at 13:02
  • Sometimes they put some empty space in the data they entered to regroup their input. This is not my choice :( – J4N Apr 14 '15 at 18:55
  • Then I would create a pivot of the data in the primary key, then reference the pivot. – sicarius92 Apr 16 '15 at 9:51
  • For details on how to do this see link. The updating goes via Ctrl+Alt+F5, if there are other data tables refreshing from there too feel free to use VBE events and subs. Note, there are special settings for the pivot to get the most of your data. There's a filter applied for null values. – sicarius92 Apr 16 '15 at 10:03

If you skip lines than list won't be auto extended.

I think the only solution here is to use user defined function in the definition of your range.

  • I see, I'm not very familiar on how Excel interacts with its VBA code, how should I call a method I would have created? – J4N Apr 9 '15 at 9:36
  • just as you do it with built-in functions, e.g. =offset("A1",0,0,lastRow(),<number of columns in your range>) – Máté Juhász Apr 9 '15 at 10:08
  • In fact I had to put it in a module to make it work. So now my only issue is how to use this method result to filter the initial range? – J4N Apr 9 '15 at 10:18
  • sorry, then your original question is not clear. What do you want to do? Filter list by what? Or use data validation?? please edit your question to make it clear. – Máté Juhász Apr 9 '15 at 12:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.