Use of CHOOSE? appropriate?

Need to shorten this to add a few more possibilities at the moment it will
only handle "EI" and "EW" but I need it to handle 3 or four more. Was
thinking a table of some sort based on the piece of the lookup cell I want to
look at but have no real idea of where to start

Hope you can follow the arguments it is very much on the edge of my own
experience level

=IF(ISERROR(FIND("EW",'Previous Project'!A5)),IF(ISERROR(FIND("EI",'Previous
Project'!A5)),'Previous Project'!A5,IF(ISERROR(FIND("R",'Previous
Project'!A5)),REPLACE('Previous Project'!A5,SEARCH("-",'Previous
Project'!A5),1,"*-"),REPLACE('Previous Project'!A5,(SEARCH("R",'Previous
Project'!A5))+1,SEARCH("-",'Previous Project'!A5)-SEARCH("R",'Previous
Project'!A5),"*-")))," ")

Hi RADIOOZ,

Your formula indicates that the relationship between the '"EW" and "EI"
tests is hierarchical. If so, you may have to stick with the nested IF
tests. But be aware you're limited to 7 of those.

If the tests aren't meant to be hierarchical, you may be able to use:
=IF(OR(ISERROR(FIND("EW",A5),ISERROR(FIND("EI",A5))),A5, etc
if any one of the error conditions is sufficient to trigger the 'TRUE'
response, or
=IF(AND(ISERROR(FIND("EW",A5),ISERROR(FIND("EI",A5))),A5, etc
if all of the error conditions is required to trigger the 'TRUE' response.

Cheers
PS: Add your "'Previous Project'!" strings - I omitted them for clarity.
Alternatively, name the source cell/range and use that instead of the
"'Previous Project'!A5" reference altogether.


"RADIOOZ" <[email protected]> wrote in message
news:[email protected]...
> Need to shorten this to add a few more possibilities at the moment it will
> only handle "EI" and "EW" but I need it to handle 3 or four more. Was
> thinking a table of some sort based on the piece of the lookup cell I want
to
> look at but have no real idea of where to start
>
> Hope you can follow the arguments it is very much on the edge of my own
> experience level
>
> =IF(ISERROR(FIND("EW",'Previous
Project'!A5)),IF(ISERROR(FIND("EI",'Previous
> Project'!A5)),'Previous Project'!A5,IF(ISERROR(FIND("R",'Previous
> Project'!A5)),REPLACE('Previous Project'!A5,SEARCH("-",'Previous
> Project'!A5),1,"*-"),REPLACE('Previous Project'!A5,(SEARCH("R",'Previous
> Project'!A5))+1,SEARCH("-",'Previous Project'!A5)-SEARCH("R",'Previous
> Project'!A5),"*-")))," ")

Macropod
Your assumption that the tests are not heirarchical is correct however if
using your formula how do I get the differing processes resulting from the EW
or EI triggered response? ie "EW" triggers a space insertion and "EI"
triggers the further IF statement to differentiate between and EI with a
revision status and one without.

"macropod" wrote:

> Hi RADIOOZ,
>
> Your formula indicates that the relationship between the '"EW" and "EI"
> tests is hierarchical. If so, you may have to stick with the nested IF
> tests. But be aware you're limited to 7 of those.
>
> If the tests aren't meant to be hierarchical, you may be able to use:
> =IF(OR(ISERROR(FIND("EW",A5),ISERROR(FIND("EI",A5))),A5, etc
> if any one of the error conditions is sufficient to trigger the 'TRUE'
> response, or
> =IF(AND(ISERROR(FIND("EW",A5),ISERROR(FIND("EI",A5))),A5, etc
> if all of the error conditions is required to trigger the 'TRUE' response.
>
> Cheers
> PS: Add your "'Previous Project'!" strings - I omitted them for clarity.
> Alternatively, name the source cell/range and use that instead of the
> "'Previous Project'!A5" reference altogether.
>
>
> "RADIOOZ" <[email protected]> wrote in message
> news:[email protected]...
> > Need to shorten this to add a few more possibilities at the moment it will
> > only handle "EI" and "EW" but I need it to handle 3 or four more. Was
> > thinking a table of some sort based on the piece of the lookup cell I want
> to
> > look at but have no real idea of where to start
> >
> > Hope you can follow the arguments it is very much on the edge of my own
> > experience level
> >
> > =IF(ISERROR(FIND("EW",'Previous
> Project'!A5)),IF(ISERROR(FIND("EI",'Previous
> > Project'!A5)),'Previous Project'!A5,IF(ISERROR(FIND("R",'Previous
> > Project'!A5)),REPLACE('Previous Project'!A5,SEARCH("-",'Previous
> > Project'!A5),1,"*-"),REPLACE('Previous Project'!A5,(SEARCH("R",'Previous
> > Project'!A5))+1,SEARCH("-",'Previous Project'!A5)-SEARCH("R",'Previous
> > Project'!A5),"*-")))," ")
>
>
>
>

In that case, they are hierarchical.

Cheers


"RADIOOZ" <[email protected]> wrote in message
news:[email protected]...
> Macropod
> Your assumption that the tests are not heirarchical is correct however if
> using your formula how do I get the differing processes resulting from the
EW
> or EI triggered response? ie "EW" triggers a space insertion and "EI"
> triggers the further IF statement to differentiate between and EI with a
> revision status and one without.
>
> "macropod" wrote:
>
> > Hi RADIOOZ,
> >
> > Your formula indicates that the relationship between the '"EW" and "EI"
> > tests is hierarchical. If so, you may have to stick with the nested IF
> > tests. But be aware you're limited to 7 of those.
> >
> > If the tests aren't meant to be hierarchical, you may be able to use:
> > =IF(OR(ISERROR(FIND("EW",A5),ISERROR(FIND("EI",A5))),A5, etc
> > if any one of the error conditions is sufficient to trigger the 'TRUE'
> > response, or
> > =IF(AND(ISERROR(FIND("EW",A5),ISERROR(FIND("EI",A5))),A5, etc
> > if all of the error conditions is required to trigger the 'TRUE'
response.
> >
> > Cheers
> > PS: Add your "'Previous Project'!" strings - I omitted them for clarity.
> > Alternatively, name the source cell/range and use that instead of the
> > "'Previous Project'!A5" reference altogether.
> >
> >
> > "RADIOOZ" <[email protected]> wrote in message
> > news:[email protected]...
> > > Need to shorten this to add a few more possibilities at the moment it
will
> > > only handle "EI" and "EW" but I need it to handle 3 or four more. Was
> > > thinking a table of some sort based on the piece of the lookup cell I
want
> > to
> > > look at but have no real idea of where to start
> > >
> > > Hope you can follow the arguments it is very much on the edge of my
own
> > > experience level
> > >
> > > =IF(ISERROR(FIND("EW",'Previous
> > Project'!A5)),IF(ISERROR(FIND("EI",'Previous
> > > Project'!A5)),'Previous Project'!A5,IF(ISERROR(FIND("R",'Previous
> > > Project'!A5)),REPLACE('Previous Project'!A5,SEARCH("-",'Previous
> > > Project'!A5),1,"*-"),REPLACE('Previous
Project'!A5,(SEARCH("R",'Previous
> > > Project'!A5))+1,SEARCH("-",'Previous Project'!A5)-SEARCH("R",'Previous
> > > Project'!A5),"*-")))," ")
> >
> >
> >
> >

Sorry I didn't understand the meaning of the word in this context obviously.
S'pose that's what I get for diving in the deep end and trying to swim before
I have learn't to do so.

"macropod" wrote:

> In that case, they are hierarchical.
>
> Cheers
>
>
> "RADIOOZ" <[email protected]> wrote in message
> news:[email protected]...
> > Macropod
> > Your assumption that the tests are not heirarchical is correct however if
> > using your formula how do I get the differing processes resulting from the
> EW
> > or EI triggered response? ie "EW" triggers a space insertion and "EI"
> > triggers the further IF statement to differentiate between and EI with a
> > revision status and one without.
> >
> > "macropod" wrote:
> >
> > > Hi RADIOOZ,
> > >
> > > Your formula indicates that the relationship between the '"EW" and "EI"
> > > tests is hierarchical. If so, you may have to stick with the nested IF
> > > tests. But be aware you're limited to 7 of those.
> > >
> > > If the tests aren't meant to be hierarchical, you may be able to use:
> > > =IF(OR(ISERROR(FIND("EW",A5),ISERROR(FIND("EI",A5))),A5, etc
> > > if any one of the error conditions is sufficient to trigger the 'TRUE'
> > > response, or
> > > =IF(AND(ISERROR(FIND("EW",A5),ISERROR(FIND("EI",A5))),A5, etc
> > > if all of the error conditions is required to trigger the 'TRUE'
> response.
> > >
> > > Cheers
> > > PS: Add your "'Previous Project'!" strings - I omitted them for clarity.
> > > Alternatively, name the source cell/range and use that instead of the
> > > "'Previous Project'!A5" reference altogether.
> > >
> > >
> > > "RADIOOZ" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Need to shorten this to add a few more possibilities at the moment it
> will
> > > > only handle "EI" and "EW" but I need it to handle 3 or four more. Was
> > > > thinking a table of some sort based on the piece of the lookup cell I
> want
> > > to
> > > > look at but have no real idea of where to start
> > > >
> > > > Hope you can follow the arguments it is very much on the edge of my
> own
> > > > experience level
> > > >
> > > > =IF(ISERROR(FIND("EW",'Previous
> > > Project'!A5)),IF(ISERROR(FIND("EI",'Previous
> > > > Project'!A5)),'Previous Project'!A5,IF(ISERROR(FIND("R",'Previous
> > > > Project'!A5)),REPLACE('Previous Project'!A5,SEARCH("-",'Previous
> > > > Project'!A5),1,"*-"),REPLACE('Previous
> Project'!A5,(SEARCH("R",'Previous
> > > > Project'!A5))+1,SEARCH("-",'Previous Project'!A5)-SEARCH("R",'Previous
> > > > Project'!A5),"*-")))," ")
> > >
> > >
> > >
> > >
>
>
>

Have kind of overcome problem by utilising another column linked to the
results of original column, however it seems very clumsy as I now have 4
colums linked in a line in order to check all variables

"macropod" wrote:

> In that case, they are hierarchical.
>
> Cheers
>
>
> "RADIOOZ" <[email protected]> wrote in message
> news:[email protected]...
> > Macropod
> > Your assumption that the tests are not heirarchical is correct however if
> > using your formula how do I get the differing processes resulting from the
> EW
> > or EI triggered response? ie "EW" triggers a space insertion and "EI"
> > triggers the further IF statement to differentiate between and EI with a
> > revision status and one without.
> >
> > "macropod" wrote:
> >
> > > Hi RADIOOZ,
> > >
> > > Your formula indicates that the relationship between the '"EW" and "EI"
> > > tests is hierarchical. If so, you may have to stick with the nested IF
> > > tests. But be aware you're limited to 7 of those.
> > >
> > > If the tests aren't meant to be hierarchical, you may be able to use:
> > > =IF(OR(ISERROR(FIND("EW",A5),ISERROR(FIND("EI",A5))),A5, etc
> > > if any one of the error conditions is sufficient to trigger the 'TRUE'
> > > response, or
> > > =IF(AND(ISERROR(FIND("EW",A5),ISERROR(FIND("EI",A5))),A5, etc
> > > if all of the error conditions is required to trigger the 'TRUE'
> response.
> > >
> > > Cheers
> > > PS: Add your "'Previous Project'!" strings - I omitted them for clarity.
> > > Alternatively, name the source cell/range and use that instead of the
> > > "'Previous Project'!A5" reference altogether.
> > >
> > >
> > > "RADIOOZ" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Need to shorten this to add a few more possibilities at the moment it
> will
> > > > only handle "EI" and "EW" but I need it to handle 3 or four more. Was
> > > > thinking a table of some sort based on the piece of the lookup cell I
> want
> > > to
> > > > look at but have no real idea of where to start
> > > >
> > > > Hope you can follow the arguments it is very much on the edge of my
> own
> > > > experience level
> > > >
> > > > =IF(ISERROR(FIND("EW",'Previous
> > > Project'!A5)),IF(ISERROR(FIND("EI",'Previous
> > > > Project'!A5)),'Previous Project'!A5,IF(ISERROR(FIND("R",'Previous
> > > > Project'!A5)),REPLACE('Previous Project'!A5,SEARCH("-",'Previous
> > > > Project'!A5),1,"*-"),REPLACE('Previous
> Project'!A5,(SEARCH("R",'Previous
> > > > Project'!A5))+1,SEARCH("-",'Previous Project'!A5)-SEARCH("R",'Previous
> > > > Project'!A5),"*-")))," ")
> > >
> > >
> > >
> > >
>
>
>