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 |
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 |
All times are GMT +1. The time now is 04:24 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com