Strip out first cell / Validation list / blanks

[andydtaylor]

Hi,

I have a table of legal entities (L.E) and divisions in those entities, which I do not want to transform. I am aiming to produce a drop-down of relevant divisions for selection using data validation in another cell.

Here is my dynamic range:

li_Div=INDEX(i_h_Division,MATCH(template!$B16,i_h_Division[LE ID],0),)


So L.E. is entered in one cell e.g. A002. The cell to the right using the validation/ named range above is showing:

A002
Div1
Div2
Div3

Is there a nice way to trim the first cell? i.e. not have L.E at the top? I am seeking not to reference column names in my named range because the next tables I do this on are more at risk of growing on refresh. Maybe offset? Is there a way to refer to column numbers /highest column number instead of names?

Also, my drop-down items include blanks (Nulls from Power Query). But I have the box "Ignore blank" checked. Is there a nice way to screen out these blanks?

Thanks,


Andy