Prev: FINDING THE LAST ENTERED NUMBER IN A ROW OF DATA
Next: Protecting worksheet
From: Ragdyer on 5 Jul 2008 17:51
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

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Karoline" <Karoline(a)discussions.microsoft.com> wrote in message
news:DF07AD1F-DA8B-41C5-92F5-891A89FF594C(a)microsoft.com...
> OH MY GOD!!!!
>
> IT WORKED! I am so excited!!!
> Thanks a LOOOOOOT and I send you a big HUG!!!
> You have no Idea how much you helped me!!!
>
> All the best!!!
> --
> KBZ
>
>
> "Mike H" wrote:
>
> > Hi,
> >
> > If I've understood correctly then you could try this. Build a table
> > somewhere out of the way looking something like this:-
> >
> > 1 Dance
> > 11 Music
> > 111 Song
> > 1V This
> > V That
> > VI The
> > V111 Other
> > V111 Rock
> > 1X Pop
> > X Classical
> >
> > In my case it's in I1 to J10
> > The this formula in B1
> > =VLOOKUP(A1,$I$1:$J$10,2,FALSE)
> >
> > Now if you type your Roman number in A1 it will return the adjacent text
> > from the table. i.e 1X returns Pop.
> >
> > Drag the formula down as required.
> >
> > Mike
> >
> > "Karoline" wrote:
> >
> > > HI! I am a dummy beginner but very interested in improve my skills. I
have
> > > been 4 hours trying to understand the logic on multiple criteria
formulas and
> > > I am afraid I am almost going to give up... Someone around could
PLEASE help
> > > me?
> > >
> > > Suppose I have 10 different type of groups to classify a loooong list
of
> > > students according to their interests as follows:
> > >
> > > A1: A10 B1:B2
> > > I painting
> > > II dancing
> > > ... ...
> > > X reading
> > >
> > > My first question is if I can relate text information (I, II, III,
IV...X)
> > > with other text info (B1:B10) so I can obtain an automatic fill of one
colum
> > > A or B...
> > > I mean if after I write for example painting the opposite colum will
> > > automatically write the group (I) or the opposite: if I introduce the
group
> > > i.e. II, inmediately I can know the activity (dancing)?
> > > I had used a replacement tool before, with other kind of tables BUT
after
> > > finishing entering all the information in the chart : I mean after
finishing
> > > a list I have used the IF (A1:A10="done"), OK, "error"),
> > > how ever this time I can have 10 diferent possibilities "I", "II",
> > > "III"....."X" and I have being giving circles to relate both columns
in a
> > > single step.
> > >
> > > As the experts of this space might have notice due to my long mail, I
have
> > > few experience, but would really appreciate some help in this! ! !
> > >
> > > ps: So far I am deciding one by one the student activity, but if you
teach
> > > me, my hope is to make a formula in such a way that with a combination
of
> > > letters I could know faster the best group to locate each student, by
> > > defining the criterias (i.e. the student will paint if he love
painting, is
> > > not interested in physical activities and has a scolarship---> LP, NO
Phy and
> > > SCHP...
> > >
> > > I will wait to see if someone can help me at the other side of the
world.
> > > Thank you,
> > >
> > > Karoline
> > >
> > > KBZ

From: Karoline on 5 Jul 2008 18:50
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
>
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------
>
> "Karoline" <Karoline(a)discussions.microsoft.com> wrote in message
> news:DF07AD1F-DA8B-41C5-92F5-891A89FF594C(a)microsoft.com...
> > OH MY GOD!!!!
> >
> > IT WORKED! I am so excited!!!
> > Thanks a LOOOOOOT and I send you a big HUG!!!
> > You have no Idea how much you helped me!!!
> >
> > All the best!!!
> > --
> > KBZ
> >
> >
> > "Mike H" wrote:
> >
> > > Hi,
> > >
> > > If I've understood correctly then you could try this. Build a table
> > > somewhere out of the way looking something like this:-
> > >
> > > 1 Dance
> > > 11 Music
> > > 111 Song
> > > 1V This
> > > V That
> > > VI The
> > > V111 Other
> > > V111 Rock
> > > 1X Pop
> > > X Classical
> > >
> > > In my case it's in I1 to J10
> > > The this formula in B1
> > > =VLOOKUP(A1,$I$1:$J$10,2,FALSE)
> > >
> > > Now if you type your Roman number in A1 it will return the adjacent text
> > > from the table. i.e 1X returns Pop.
> > >
> > > Drag the formula down as required.
> > >
> > > Mike
> > >
> > > "Karoline" wrote:
> > >
> > > > HI! I am a dummy beginner but very interested in improve my skills. I
> have
> > > > been 4 hours trying to understand the logic on multiple criteria
> formulas and
> > > > I am afraid I am almost going to give up... Someone around could
> PLEASE help
> > > > me?
> > > >
> > > > Suppose I have 10 different type of groups to classify a loooong list
> of
> > > > students according to their interests as follows:
> > > >
> > > > A1: A10 B1:B2
> > > > I painting
> > > > II dancing
> > > > ... ...
> > > > X reading
> > > >
> > > > My first question is if I can relate text information (I, II, III,
> IV...X)
> > > > with other text info (B1:B10) so I can obtain an automatic fill of one
> colum
> > > > A or B...
> > > > I mean if after I write for example painting the opposite colum will
> > > > automatically write the group (I) or the opposite: if I introduce the
> group
> > > > i.e. II, inmediately I can know the activity (dancing)?
> > > > I had used a replacement tool before, with other kind of tables BUT
> after
> > > > finishing entering all the information in the chart : I mean after
> finishing
> > > > a list I have used the IF (A1:A10="done"), OK, "error"),
> > > > how ever this time I can have 10 diferent possibilities "I", "II",
> > > > "III"....."X" and I have being giving circles to relate both columns
> in a
> > > > single step.
> > > >
> > > > As the experts of this space might have notice due to my long mail, I
> have
> > > > few experience, but would really appreciate some help in this! ! !
> > > >
> > > > ps: So far I am deciding one by one the student activity, but if you
> teach
> > > > me, my hope is to make a formula in such a way that with a combination
> of
> > > > letters I could know faster the best group to locate each student, by
> > > > defining the criterias (i.e. the student will paint if he love
> painting, is
> > > > not interested in physical activities and has a scolarship---> LP, NO
> Phy and
> > > > SCHP...
> > > >
> > > > I will wait to see if someone can help me at the other side of the
> world.
> > > > Thank you,
> > > >
> > > > Karoline
> > > >
> > > > KBZ
>
>
From: Mike H on 5 Jul 2008 19:03
you seem to be pleased with my suggestion and I'm glad I could help. thank
you for the feedback.

"Karoline" wrote:

> OH MY GOD!!!!
>
> IT WORKED! I am so excited!!!
> Thanks a LOOOOOOT and I send you a big HUG!!!
> You have no Idea how much you helped me!!!
>
> All the best!!!
> --
> KBZ
>
>
> "Mike H" wrote:
>
> > Hi,
> >
> > If I've understood correctly then you could try this. Build a table
> > somewhere out of the way looking something like this:-
> >
> > 1 Dance
> > 11 Music
> > 111 Song
> > 1V This
> > V That
> > VI The
> > V111 Other
> > V111 Rock
> > 1X Pop
> > X Classical
> >
> > In my case it's in I1 to J10
> > The this formula in B1
> > =VLOOKUP(A1,$I$1:$J$10,2,FALSE)
> >
> > Now if you type your Roman number in A1 it will return the adjacent text
> > from the table. i.e 1X returns Pop.
> >
> > Drag the formula down as required.
> >
> > Mike
> >
> > "Karoline" wrote:
> >
> > > HI! I am a dummy beginner but very interested in improve my skills. I have
> > > been 4 hours trying to understand the logic on multiple criteria formulas and
> > > I am afraid I am almost going to give up... Someone around could PLEASE help
> > > me?
> > >
> > > Suppose I have 10 different type of groups to classify a loooong list of
> > > students according to their interests as follows:
> > >
> > > A1: A10 B1:B2
> > > I painting
> > > II dancing
> > > ... ...
> > > X reading
> > >
> > > My first question is if I can relate text information (I, II, III, IV...X)
> > > with other text info (B1:B10) so I can obtain an automatic fill of one colum
> > > A or B...
> > > I mean if after I write for example painting the opposite colum will
> > > automatically write the group (I) or the opposite: if I introduce the group
> > > i.e. II, inmediately I can know the activity (dancing)?
> > > I had used a replacement tool before, with other kind of tables BUT after
> > > finishing entering all the information in the chart : I mean after finishing
> > > a list I have used the IF (A1:A10="done"), OK, "error"),
> > > how ever this time I can have 10 diferent possibilities "I", "II",
> > > "III"....."X" and I have being giving circles to relate both columns in a
> > > single step.
> > >
> > > As the experts of this space might have notice due to my long mail, I have
> > > few experience, but would really appreciate some help in this! ! !
> > >
> > > ps: So far I am deciding one by one the student activity, but if you teach
> > > me, my hope is to make a formula in such a way that with a combination of
> > > letters I could know faster the best group to locate each student, by
> > > defining the criterias (i.e. the student will paint if he love painting, is
> > > not interested in physical activities and has a scolarship---> LP, NO Phy and
> > > SCHP...
> > >
> > > I will wait to see if someone can help me at the other side of the world.
> > > Thank you,
> > >
> > > Karoline
> > >
> > > KBZ
From: Pete_UK on 5 Jul 2008 20:21
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: Karoline on 5 Jul 2008 22:07
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
>
First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: FINDING THE LAST ENTERED NUMBER IN A ROW OF DATA
Next: Protecting worksheet