Data Validation

[Duncan]

I use Office Excel 2008 for Mac.

I have a payment voucher worksheet with a list of Payees with name,
address, street, city, province, postal code, country, attention in
columns. I have created table names PAYEESList and PAYEESLookUp.

In INSERT > NAME > DEFINE, I typed in the following in the Source
space:
PAYEESList - =OFFSET(PAYEES!$A$2,0,0,CONTRA(PAYEES!$A:$A)-1,1)
PAYEESLookUp - =OFFSET(PAYEESList,,,,8)

Because of the number of Payees, I would like to be able to create,
through Data Validation a dropdown menu (A) in which I can select one
of the letters of the alphabet with another dropdown menu (B)
dependent on (A) select only those Payees beginning with that letter.
Any suggestions?

I tried to use the following formula unsuccessfully:
=IF(LOOKUP((ISERROR(LEFT(A2,1))),PAYEESLookUp,2,FALSE)='INPUT FORM'!
AC2,LOOKUP(A2,PAYEESLookUp,2,False),"")

ISERROR(LEFT(A2,1) refers to the table PAYEESList where I compare the
first letter of that item in the table and compare it with the
alphabet letter in a a dropdown menu I created for all the 26 letters
of the alphabet.

Duncan

 

[Bernie Deitrick]

Duncan,

In a cell, say M2, array enter the formula (enter using Ctrl-Shift-Enter)

=IF(COUNTIF(payeeslist,$F$2&"*")>=ROW()-ROW($B$1),INDEX(payeeslist,
LARGE((LEFT(payeeslist,1)=$F$2)*(ROW(payeeslist)-1),
COUNTIF(payeeslist,$F$2&"*")-ROW()+ROW($M$2))),"")

Change the $F$2 to the cell that contains your single letter.
Then copy down for as far as you need (at least as far as your most frequent "first letter" payee
count). Once you have enough cells, they will show as blank.

Then create another name, ShortList, using the formula

=OFFSET(PAYEES!$M$2,0,0,SUMPRODUCT(--(PAYEES!$M$2:$M$100<>"")),1)

If you expect more than 100, increase the $100 to account for that - you can use a big number, but
you don't need to....

HTH,
Bernie
MS Excel MVP