=NameCheck - Monday???
in [Functions]
|
From: Lost Cluster on 23 May 2010 10:25 How do you write a formula for this: =NameCheck - Monday I'm looking for the correct syntax for writing a formula that deletes or omits names from a list? For example I have a list with the names of all my employees called NameCheck and another list called Monday. I want the names in the Monday list to NOT show up on my data validation source that currently is =NameCheck. Thanks
From: T. Valko on 23 May 2010 12:11 You'd have to create a new list and use that as the source for your drop down. One way... Let's assume NameCheck is in column A and Monday is in column B. Let's create the new list starting in cell D2. So, enter this array formula** in D2 and copy down until you get #NUM! errors: =INDEX(NameCheck,SMALL(IF(ISNA(MATCH(NameCheck,Monday,0)),ROW(NameCheck)),ROWS(D$2:D2))-MIN(ROW(NameCheck))+1) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Then, as the source for the drop down use: =$D$2:INDEX($D$2:$D$100,COUNTIF($D$2:$D$100,"*")) Adjust for a reasonable end of range D100 Or, give that formula a defined name like NewList and use =NewList as the source. -- Biff Microsoft Excel MVP "Lost Cluster" <LostCluster(a)discussions.microsoft.com> wrote in message news:932BE404-43C4-4478-887F-1374A8BBBDEF(a)microsoft.com... > How do you write a formula for this: > > =NameCheck - Monday > > I'm looking for the correct syntax for writing a formula that deletes or > omits names from a list? For example I have a list with the names of all > my > employees called NameCheck and another list called Monday. I want the > names > in the Monday list to NOT show up on my data validation source that > currently > is =NameCheck. > Thanks >
From: Lost Cluster on 23 May 2010 22:41 Thank you, it works!!! "T. Valko" wrote: > You'd have to create a new list and use that as the source for your drop > down. > > One way... > > Let's assume NameCheck is in column A and Monday is in column B. > > Let's create the new list starting in cell D2. So, enter this array > formula** in D2 and copy down until you get #NUM! errors: > > =INDEX(NameCheck,SMALL(IF(ISNA(MATCH(NameCheck,Monday,0)),ROW(NameCheck)),ROWS(D$2:D2))-MIN(ROW(NameCheck))+1) > > ** array formulas need to be entered using the key combination of > CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT > key then hit ENTER. > > Then, as the source for the drop down use: > > =$D$2:INDEX($D$2:$D$100,COUNTIF($D$2:$D$100,"*")) > > Adjust for a reasonable end of range D100 > > Or, give that formula a defined name like NewList and use =NewList as the > source. > > -- > Biff > Microsoft Excel MVP > > > "Lost Cluster" <LostCluster(a)discussions.microsoft.com> wrote in message > news:932BE404-43C4-4478-887F-1374A8BBBDEF(a)microsoft.com... > > How do you write a formula for this: > > > > =NameCheck - Monday > > > > I'm looking for the correct syntax for writing a formula that deletes or > > omits names from a list? For example I have a list with the names of all > > my > > employees called NameCheck and another list called Monday. I want the > > names > > in the Monday list to NOT show up on my data validation source that > > currently > > is =NameCheck. > > Thanks > > > > > . >
From: T. Valko on 24 May 2010 09:28 You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "Lost Cluster" <LostCluster(a)discussions.microsoft.com> wrote in message news:CAE8B699-4253-4426-8318-0EC3B88F90C0(a)microsoft.com... > Thank you, it works!!! > > "T. Valko" wrote: > >> You'd have to create a new list and use that as the source for your drop >> down. >> >> One way... >> >> Let's assume NameCheck is in column A and Monday is in column B. >> >> Let's create the new list starting in cell D2. So, enter this array >> formula** in D2 and copy down until you get #NUM! errors: >> >> =INDEX(NameCheck,SMALL(IF(ISNA(MATCH(NameCheck,Monday,0)),ROW(NameCheck)),ROWS(D$2:D2))-MIN(ROW(NameCheck))+1) >> >> ** array formulas need to be entered using the key combination of >> CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the >> SHIFT >> key then hit ENTER. >> >> Then, as the source for the drop down use: >> >> =$D$2:INDEX($D$2:$D$100,COUNTIF($D$2:$D$100,"*")) >> >> Adjust for a reasonable end of range D100 >> >> Or, give that formula a defined name like NewList and use =NewList as the >> source. >> >> -- >> Biff >> Microsoft Excel MVP >> >> >> "Lost Cluster" <LostCluster(a)discussions.microsoft.com> wrote in message >> news:932BE404-43C4-4478-887F-1374A8BBBDEF(a)microsoft.com... >> > How do you write a formula for this: >> > >> > =NameCheck - Monday >> > >> > I'm looking for the correct syntax for writing a formula that deletes >> > or >> > omits names from a list? For example I have a list with the names of >> > all >> > my >> > employees called NameCheck and another list called Monday. I want the >> > names >> > in the Monday list to NOT show up on my data validation source that >> > currently >> > is =NameCheck. >> > Thanks >> > >> >> >> . >>
|
Pages: 1 Prev: how to unshared Next: Conditonal Format based on multiple criteria |