0

I have two column, W and R. W is the company name, and R is the test type of the company.They are in the sheet "List"

When I type the company name at column W, I can choose the list in column R, which has the test type of the company. For example, If I type "Batman" in W3, in the cell R3, I can choose "Batcar", "Bat-shirt".

The test type of the company is stored in another sheet called "Batman", located at column D.It would automatic update base on a database. When I add a new test type in the database, column D of the test type sheets would automatic refresh.

In "Login", at column R, I use a data validation to generate the list, It refer to "=INDIRECT(List!$W2322)". And in name manager, let "Batman" be the campany name, the name range would be "=Batman!$D:$D"

But in the list, it would start at the bottom with so many blanks. I want the list start at the beginning, and end with the last item in column D, no more blanks.

Can anyone help me?

Hui Man Chang
  • 7
  • 4
  • 1
    You need to include worksheet name for dynamic named range using `INDIRECT()`. Alternatively you can make the range to be a real Excel table, then named range to something like `=YourTableName[ColumnTitle]` – PatricK May 23 '16 at 03:09
  • In the cell which display list, I have already write "=INDIRECT($W$2322)". Is it what you mean? – Hui Man Chang May 23 '16 at 03:11
  • If I make it into excel table, new item add automatically would not consider as new table item – Hui Man Chang May 23 '16 at 03:12
  • 1
    he means `=indirect('sheetname'!$W$2322)` – Forward Ed May 23 '16 at 03:13
  • There are ways to add data to an existing table via VBA. Anyway, the drop down list to be from **D1** to value of `W2322` in column D? – PatricK May 23 '16 at 03:15
  • I am confused now. Where do you need the Data Validation with drop down list? Is the list limited to this fixed range `W2322:W4322`? What is the relation to the W2322 to the dynamic named range of yours? It really depends on how you use the worksheet ranges. e.g. you can have a dynamic named range based on 2 other named ranges, it's very flexible. – PatricK May 23 '16 at 03:24
  • Let me clarify the situation. I have two column, W and R. W is the company name, and R is the test type of the company. When I type the company name at column W, I can choose the list in column R, which has the test type of the company. The test type of the company is stored in another sheet, it would automatic update base on a database. When I add a new test type in the database, column D of the test type sheets would automatic refresh. – Hui Man Chang May 23 '16 at 03:31
  • I want the list start at the beginning of the list of test type, and end with the last item in that column, no blanks. Therefore, I cannot choose the whole column as the reference – Hui Man Chang May 23 '16 at 03:33
  • So, the list items in column R depends on the value in W of the same row? You need to provide more detail about relation of company name and sets of tests the company can have. Please add details in the Post, not comment. – PatricK May 23 '16 at 03:43
  • Done. You can take a look. Thanks – Hui Man Chang May 23 '16 at 03:59
  • How you generate the list is the cause of this problem. Why don't you populate the list without blanks, so that your DV will show the data without any blanks in it. – Sixthsense May 23 '16 at 04:04
  • Because I have to add item to the database. The range will not updated after the list is add one more test type – Hui Man Chang May 23 '16 at 04:11
  • Little bit unclear about how you do the whole process. A workbook link uploaded somewhere in the web may helps us in fixing the issue. – Sixthsense May 23 '16 at 04:21
  • 1
    This has been answered before ... see [this on SO](http://stackoverflow.com/questions/20850122/generate-a-filtered-dynamic-drop-down-list?rq=1) or [this](http://www.contextures.com/exceldatavaldependindextables.html) – OldUgly May 23 '16 at 05:37
  • those answers don't quite suit OP's question, since the first assumes you already know which value to start from (and thus performing some `MATCH` over this value) while the second uses VBA. I know that the OP's added VBA tag but I assume his genuine need is to stick with Excel UI. Hence my answer – user3598756 May 23 '16 at 07:41

1 Answers1

0

change data validation list source to:

=OFFSET(INDIRECT(List!$W2322),MATCH(TRUE,INDEX(INDIRECT(List!$W2322)<>0,),0)-1,0,COUNTA(INDIRECT(List!$W2322)))

and have your list starting from the first non blank cell of the referenced column and ending after as many cells as non blank ones in it

so your non blank values must be contiguous

quite more cumbersome is the solution to also limit the list lower bound to the last non empty cell: 

=OFFSET(INDIRECT(List!$W2322),MATCH(TRUE,INDEX(INDIRECT(List!$W2322)<>0,),0)-1,0,MATCH(LOOKUP(2,1/(INDIRECT(List!$W2322)<>""),INDIRECT(List!$W2322)),INDIRECT(List!$W2322))-MATCH(TRUE,INDEX(INDIRECT(List!$W2322)<>0,),0)+1)

for the first formula I exploited Raystafarian solution from here

for the second formula I also exploited the simoco solution from here

Community
  • 1
  • 1
user3598756
  • 28,893
  • 4
  • 18
  • 28