Prev: FINDING THE LAST ENTERED NUMBER IN A ROW OF DATA
Next: Protecting worksheet
From: Ragdyer on 6 Jul 2008 01:59
You're welcome Karoline, and appreciate the feed-back.

And Pete ... What you would you like as a retainer, since you appear to be
acting as my lawyer?<bg>
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Karoline" <Karoline(a)discussions.microsoft.com> wrote in message
news:83A7724D-1849-42AF-BDBB-3EE239A7E32F(a)microsoft.com...
> Ok Pete!
> I will take note of it! Actually I tried with Ragdyer's formula (i messed
> up a bit, but finally got it) and understood your point. Thanks and
thanks
> Ragdyer too.
> --
> KBZ
>
>
> "Pete_UK" wrote:
>
> > As you use Excel more, you will discover some of the drawbacks with
> > the formula that Mike gave you. Suppose you enter a value in column A
> > that is not in the lookup table, like "A" or 123? The formula returns
> > the error #N/A which means that the value can't be found, but you
> > might want to show something else instead of the error. Another
> > situation is that you might have copied the formula down a number of
> > rows in anticipation of entering data later, but again a blank doesn't
> > exist in the lookup table so you will get an error.
> >
> > Ragdyer's formula takes account of these possible errors (and more),
> > and returns the message "No match" instead of the rather unhelpful #N/
> > A. I suggest you study it for future use.
> >
> > Pete
> >
> > On Jul 5, 11:50 pm, Karoline <Karol...(a)discussions.microsoft.com>
> > wrote:
> > > Mmmm interesting approach and step by step explanation. Thanks
Ragdyer..
> > >
> > > It seems to be that there are multiple ways to reach the result,
though I
> > > must confessed using the VLOOKUP strategy suggested by Mike H works
better
> > > for amateurs like me, since it reduce the probability to make a
mistake while
> > > inserting the data.
> > >
> > > How ever thank you very much for taking your time and sharing your
knowledge!
> > > --
> > > KBZ
> > >
> > >
> > >
> > > "Ragdyer" wrote:
> > > > Now, you mentioned being able to enter *either* a group *or* an
interest,
> > > > and have the associated value for either one displayed.
> > >
> > > > The first step is to create a datalist that will create the
associations
> > > > between the groups and the interests.
> > >
> > > > Locate this in an out-of-the-way area, say Y1 to Z10.
> > > > For this example, let's use in Y1 to Y10 regular numbers, 1 to 10.
> > > > In Z1 to Z10 list the various interests.
> > >
> > > > Say you enter a value from either category in A1, and you wish the
> > > > affiliated data to display in B1.
> > >
> > > > So, use this formula in B1:
> > >
> > > >
=IF(A1="","",IF(ISNA(MATCH(A1,Y1:Y10,0)),IF(ISNA(MATCH(A1,Z1:Z10,0)),"NO
> > > > Match",
> > > > INDEX(Y1:Y10,MATCH(A1,Z1:Z10,0))),INDEX(Z1:Z10,MATCH(A1,Y1:Y10,0))))
> > >
> > > > This should poll your datalist in *either* direction.
> > > > --
> > > > HTH,
> > >
> > > > RD
> >

From: Pete_UK on 6 Jul 2008 06:36
Ha Ha

Karoline seemed to be dismissing your formula as too complex for her,
so I just felt I needed to point out some of the extra things that
your's did compared to Mike's (which is still a fine formula !!)

Pete.

On Jul 6, 6:59 am, "Ragdyer" <RagD...(a)cutoutmsn.com> wrote:
> You're welcome Karoline, and appreciate the feed-back.
>
> And Pete ... What you would you like as a retainer, since you appear to be
> acting as my lawyer?<bg>
> --
> Regards,
>
> RD
>
First  |  Prev  | 
Pages: 1 2 3
Prev: FINDING THE LAST ENTERED NUMBER IN A ROW OF DATA
Next: Protecting worksheet