Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data Validation
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,FA LSE)='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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data Validation
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 "Duncan" wrote in message ... 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,FA LSE)='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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Validation Data using Validation Table cell range..... | Excel Discussion (Misc queries) | |||
Data Validation Update Validation Selection | Excel Worksheet Functions | |||
data validation invalid in dynamic validation list | Excel Discussion (Misc queries) | |||
data validation invalid in dynamic validation list | Excel Worksheet Functions | |||
Data validation with validation lists and combo boxs | Excel Discussion (Misc queries) |