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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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),"*-")))," ")