Re: Data Validation with repeaters excluded from the source list
From: Hari (excel_hari_at_hotmail.com)
Date: 07/21/04
- Next message: Serra: "Copying JUST the filtered rows"
- Previous message: Andy: "Re: fractional pennies"
- In reply to: Hari: "Re: Data Validation with repeaters excluded from the source list"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 21 Jul 2004 21:57:05 +0530
Hi Frank,
I thought of a different formula in my case which is working fine.
I have eliminated the helper column.
As said before I have the source data in Column B starting from row no 3 and
Im creating a dropdown list in Column AR starting from rowno3.
For AR 3 I put formula as --> =IF(B3<>0,B3,"")
For all rows starting from row 4 in column AR I put the formula
as -->=IF(COUNTIF($AR$3:AR3,B4)=0,IF(B4="","",B4),"")
Though my problem is solved I have a request from ur side.
I would like to LEARN your logic especially why u have done it that way and
more importantly how your formula achieves what u wanted to do. Like Im not
able to understand how one can have a formula like -->
COUNTIF(B$1:B1,$A$1:$A$100) where both range and critirea's are arrays. I
did not know that criteria could be an array. Also even criteria could be an
array why is it that here range is a smaller array as compared to criteria.
( Logic wise isnt range supposed to be a SUPERSET of criteria).
Also u have used the above Countif along with match. But for the Match u
specify the lookup value as Zero. Why is that zero.
Please shed some light so that I may better understand how u people
manipulate formulas to achieve what u want to do.
Regards,
Hari
India
"Hari" <excel_hari@hotmail.com> wrote in message
news:eQixCwzbEHA.3420@TK2MSFTNGP12.phx.gbl...
> Hi Frank,
>
> Im having some problems with this formula....
>
> My initial repeater list is in column B starting from row no 3.
>
> I created a helper list in column AQ starting from row no 3.
>
> So in the array formula u have given I changed $A$1 to $B43, $A$100 to
> $B$100, B$1 to AQ$3 and B1 to AQ1.
>
> Then i copied this formula till AQ 100.
>
> But the result im getting is a blank. I used excel's evaluate formula
> option (I have 2002) and saw that where the countif is being evaluated
> (inside Match function) the formula evaluates to N#A for all cells. I dont
> understand the logic behind having look up value in Match as Zero. May be
> thats why the problem is coming (Im just speculating, pls correct me if my
> understanding is woefully inadequate).
>
> Also please note my original repeater list also have some blanks in some
of
> the rows.
>
> I have pasted my raw (Dummy data ) extending from B3 to B25 below.
>
> Column B
>
> 123
> 123
> 123
> 123
> 123
> 123
> 123
> 123
> 123
> 345
> 345
> 345
> 345
>
>
> 345
> 345
> 123
> 123
> 345
> 345
> 345
> 345
>
>
>
> Please guide me.
>
> Regards,
> Hari
> India
> "Frank Kabel" <frank.kabel@freenet.de> wrote in message
> news:eRnpMhdbEHA.3996@TK2MSFTNGP12.phx.gbl...
> > Hi
> > one way: Use a helper column. e.g. column B and enter the following
> > formulas
> > B1:
> > =A1
> >
> > B2: Enter the array formula (entered with CTRL+SHIFT+ENTER:
> > =IF(ISERROR(INDEX($A$1:$A$100,MATCH(0,COUNTIF(B$1:B1,$A$1:$A$100),0))),
> > "",INDEX($A$1:$A$100,MATCH(0,COUNTIF(B$1:B1,$A$1:$A$100),0)))
> > and copy B2 down as far as needed.
> > Use this column as your column for the data validation list
> >
> > --
> > Regards
> > Frank Kabel
> > Frankfurt, Germany
> >
> > "Hari" <excel_hari@hotmail.com> schrieb im Newsbeitrag
> > news:uZDgmOcbEHA.1000@TK2MSFTNGP12.phx.gbl...
> > > Hi,
> > >
> > > In sheet 1 I have data in Column A starting from row No 2 like :-
> > >
> > > Aw
> > > Er
> > > Aw
> > > Rt
> > > Yu
> > > Ui
> > > Rt
> > > Aw
> > > Ui
> > >
> > > Now, the above list which is in column A will have its rows
> > expanding. That
> > > is today the data may be till Row 300 and tomorrow it will/might
> > > increase to 500 and after some time it may go to even 2000 or so.Now
> > many of
> > > the records in column 1 are repeating. Like U may notice "Aw", "Rt"
> > etc more
> > > than once.
> > >
> > > The "solution" I desire is in Sheet 2 Column C starting from Row 2, I
> > want
> > > to create a dropdown (validation list) based on the data in column A
> > of
> > > sheet 1
> > > But since there are many repeaters in Column A of sheet 1 I want that
> > the
> > > list in sheet 2 to have only one instance. That is "Aw", "Rt" etc
> > should
> > > figure only once in the dropdown.
> > >
> > > Please guide me.
> > >
> > > ( I went thru Debra Dalgleish's site on Validation ... but couldnt
> > find what
> > > I was looking for --I might have missed something .)
> > >
> > > Regards,
> > > Hari
> > > India
> > >
> > >
> >
>
>
- Next message: Serra: "Copying JUST the filtered rows"
- Previous message: Andy: "Re: fractional pennies"
- In reply to: Hari: "Re: Data Validation with repeaters excluded from the source list"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
