Pick List issue

[dranon]

I have a pick list, that varies by row, with a variable number of
columns that have valid data.

For example, the pick list might be:

NewYork Detroit SanDiego
Ontario Quebec

Let's say that the lists are organized to be on the same row of the
input cell. So, the above might be in the range of P1:R2 (where 5
cells have data and R2 is empty).

In Column A, the validation formula for the list is:

=INDEX($P$1:$R$2,ROW(),0,1)

This will give me a choice of NewYork, Detroit and SanDiego in row 1
and Ontario, Quebec and a blank entry in row 2.

Does anybody know of a way to get the pick list limited to only two
entries on row 2, so that it only gives me a choice of Ontario and
Quebec?

While it isn't a big deal when all pick lists are about the same
length, my real pick lists range from a single entry to 20 or so and I
end up with 19 blanks on some rows when everything is extended.

Thanks

 

[Shane Devenshire]

Hi,

You're not getting any responses because your question is not clear. Show
use the data layout with samples. Are you manually typing the list into the
Source box of the Data Validation command?

 

[dranon]

Hi, Shane,

You're not getting any responses because your question is not clear.

I think I'm not getting responses because it can't be done.

Show use the data layout with samples.

I already did, didn't I? See the range P1:R2, below.

Are you manually typing the list into the Source box of the Data Validation command?

I thought I mentioned that I was putting the following into the pick
list definition:

=INDEX($P$1:$R$2,ROW(),0,1)

Come back and let me know what you think isn't clear. Then I'll point

 

[dranon]

In case anybody cares, the solution is to replace the following:

=INDEX($P$1:$R$2,ROW(),0,1)

with

=OFFSET(P1:R1,0,0,1,COUNTIF(P1:R1,"> "))

This works as long as:

1) You can live with the fact that the maximum number of elements is
restricted to the number of columns from P through (max columns)
[depends on EXCEL version]. I will never have more than 25 or so, so
that works for me.

2) The contents of your pick list is either all text items or the
format of the cell is restricted to Text (so the "> " will count every
element)

3) There are no "blanks" in the pick lists

So, if the above is expanded on a given line so that the pick list
starts in column P and ends in column BZ, the picklist is properly
picked up with:

=OFFSET(Px:BZx,0,0,1,COUNTIF(Px:Rx,"> ")) where x is the row number.
Since the row number auto updates when copied, putting the above into
the source field for the pick list in row 2 (changing x to 2), and
then copying the validation down to as many input rows as you want to
use works quite nicely.

Thanks for trying, Shane.

 

[dranon]

Oops. One change:

In case anybody cares, the solution is to replace the following:

=INDEX($P$1:$R$2,ROW(),0,1)

with

=OFFSET(P1:R1,0,0,1,COUNTIF(P1:R1,"> "))

This works as long as:

1) You can live with the fact that the maximum number of elements is
restricted to the number of columns from P through (max columns)
[depends on EXCEL version]. I will never have more than 25 or so, so
that works for me.

2) The contents of your pick list is either all text items or the
format of the cell is restricted to Text (so the "> " will count every
element)

3) There are no "blanks" in the pick lists

So, if the above is expanded on a given line so that the pick list
starts in column P and ends in column BZ, the picklist is properly
picked up with:

=OFFSET(Px:BZx,0,0,1,COUNTIF(Px:Rx,"> "))

s/b

=OFFSET(Px:BZx,0,0,1,COUNTIF(Px:BZx,"> "))