Hi I am using the following formula in sheet Data Entry cell B14
=IF(INDEX(Sheet1!C:C,MATCH(B7,Sheet1!A:A,0),1)="AS A","NO","YES")
which works fine. But I need to add more to it as follows:

If the above also ="GIG", "NO","YES"
Plus if cell B33="ASA123456","YES","YES"

Cell B14 & B33 are on a sheet called Data Entry all the other info comes
from Sheet1

How do I add the above into my formula?

Bob

Hi!

Adding this part:

If the above also ="GIG", "NO","YES"

is no problem but you need to explain further how this part fits:

Plus if cell B33="ASA123456","YES","YES"

Unless it's a separate test:

=IF(OR(INDEX(Sheet1!C:C,MATCH(B7,Sheet1!A:A,0))={" ASA","GIG"}),"NO",IF('Data
Entry'!B33="ASA12346","YES","YES"))

Biff

"dbl" wrote in message
...
Hi I am using the following formula in sheet Data Entry cell B14
=IF(INDEX(Sheet1!C:C,MATCH(B7,Sheet1!A:A,0),1)="AS A","NO","YES")
which works fine. But I need to add more to it as follows:

If the above also ="GIG", "NO","YES"
Plus if cell B33="ASA123456","YES","YES"

Cell B14 & B33 are on a sheet called Data Entry all the other info comes
from Sheet1

How do I add the above into my formula?

Bob

Biff there is one policy number (ASA123456) which is different from the rest

ASA is "NO" apart from the above which is "YES" so I cannot in this case use
the ASA part of the policy number.

The formula you have sent works in all scenarios apart from returning "NO"
for the above policy which should be "YES".

Is this because it picks up the ASA part in the formula and is then not over
ridden by the last part? I also take it that the last "YES" is the answer
if it finds none of the above? which is how it should work.

Thanks for your help.

Bob

"Biff" wrote in message
...
Hi!

Adding this part:

If the above also ="GIG", "NO","YES"

is no problem but you need to explain further how this part fits:

Plus if cell B33="ASA123456","YES","YES"

Unless it's a separate test:

=IF(OR(INDEX(Sheet1!C:C,MATCH(B7,Sheet1!A:A,0))={" ASA","GIG"}),"NO",IF('Data
Entry'!B33="ASA12346","YES","YES"))

Biff

"dbl" wrote in message
...
Hi I am using the following formula in sheet Data Entry cell B14
=IF(INDEX(Sheet1!C:C,MATCH(B7,Sheet1!A:A,0),1)="AS A","NO","YES")
which works fine. But I need to add more to it as follows:

If the above also ="GIG", "NO","YES"
Plus if cell B33="ASA123456","YES","YES"

Cell B14 & B33 are on a sheet called Data Entry all the other info comes
from Sheet1

How do I add the above into my formula?

Bob

Hi!

Try reversing the conditions:

=IF('Data
Entry'!B33="ASA12346","YES",IF(OR(INDEX(Sheet1!C:C ,MATCH(B7,Sheet1!A:A,0))={"ASA","GIG"}),"NO","YES" ))

Biff

"dbl" wrote in message
...
Biff there is one policy number (ASA123456) which is different from the
rest

ASA is "NO" apart from the above which is "YES" so I cannot in this case
use the ASA part of the policy number.

The formula you have sent works in all scenarios apart from returning "NO"
for the above policy which should be "YES".

Is this because it picks up the ASA part in the formula and is then not
over ridden by the last part? I also take it that the last "YES" is the
answer if it finds none of the above? which is how it should work.

Thanks for your help.

Bob

"Biff" wrote in message
...
Hi!

Adding this part:

If the above also ="GIG", "NO","YES"

is no problem but you need to explain further how this part fits:

Plus if cell B33="ASA123456","YES","YES"

Unless it's a separate test:

=IF(OR(INDEX(Sheet1!C:C,MATCH(B7,Sheet1!A:A,0))={" ASA","GIG"}),"NO",IF('Data
Entry'!B33="ASA12346","YES","YES"))

Biff

"dbl" wrote in message
...
Hi I am using the following formula in sheet Data Entry cell B14
=IF(INDEX(Sheet1!C:C,MATCH(B7,Sheet1!A:A,0),1)="AS A","NO","YES")
which works fine. But I need to add more to it as follows:

If the above also ="GIG", "NO","YES"
Plus if cell B33="ASA123456","YES","YES"

Cell B14 & B33 are on a sheet called Data Entry all the other info comes
from Sheet1

How do I add the above into my formula?

Bob

Thanks Biff that works fine.

Regards Bob
"Biff" wrote in message
...
Hi!

Try reversing the conditions:

=IF('Data
Entry'!B33="ASA12346","YES",IF(OR(INDEX(Sheet1!C:C ,MATCH(B7,Sheet1!A:A,0))={"ASA","GIG"}),"NO","YES" ))

Biff

"dbl" wrote in message
...
Biff there is one policy number (ASA123456) which is different from the
rest

ASA is "NO" apart from the above which is "YES" so I cannot in this case
use the ASA part of the policy number.

The formula you have sent works in all scenarios apart from returning
"NO" for the above policy which should be "YES".

Is this because it picks up the ASA part in the formula and is then not
over ridden by the last part? I also take it that the last "YES" is the
answer if it finds none of the above? which is how it should work.

Thanks for your help.

Bob

"Biff" wrote in message
...
Hi!

Adding this part:

If the above also ="GIG", "NO","YES"

is no problem but you need to explain further how this part fits:

Plus if cell B33="ASA123456","YES","YES"

Unless it's a separate test:

=IF(OR(INDEX(Sheet1!C:C,MATCH(B7,Sheet1!A:A,0))={" ASA","GIG"}),"NO",IF('Data
Entry'!B33="ASA12346","YES","YES"))

Biff

"dbl" wrote in message
...
Hi I am using the following formula in sheet Data Entry cell B14
=IF(INDEX(Sheet1!C:C,MATCH(B7,Sheet1!A:A,0),1)="AS A","NO","YES")
which works fine. But I need to add more to it as follows:

If the above also ="GIG", "NO","YES"
Plus if cell B33="ASA123456","YES","YES"

Cell B14 & B33 are on a sheet called Data Entry all the other info
comes from Sheet1

How do I add the above into my formula?

Bob

Biff can you help sort this one out, I need to join the 2 formulas together


=IF(B39="ASA123456",INDEX(Sheet1!G:G,MATCH(B7,Shee t1!A:A,0))))

If the policy number =ASA123456 then return the value in G:G other wise
return 0 this works only if the policy number is True other wise it returns
false. If its false I need it to perform the following
which gives me the policy excess from G:G or H:H this part works fine.

=IF(INDEX(Sheet1!G:G,MATCH(B7,Sheet1!A:A,0),1)=0,I NDEX(Sheet1!H:H,MATCH(B7,Sheet1!A:A,0),1),INDEX(Sh eet1!G:G,MATCH(B7,Sheet1!A:A,0),1))

How do I bring the two together? I have spent hours trying but have made no
progress.

Thanks Bob


"dbl" wrote in message
...
Thanks Biff that works fine.

Regards Bob
"Biff" wrote in message
...
Hi!

Try reversing the conditions:

=IF('Data
Entry'!B33="ASA12346","YES",IF(OR(INDEX(Sheet1!C:C ,MATCH(B7,Sheet1!A:A,0))={"ASA","GIG"}),"NO","YES" ))

Biff

"dbl" wrote in message
...
Biff there is one policy number (ASA123456) which is different from the
rest

ASA is "NO" apart from the above which is "YES" so I cannot in this case
use the ASA part of the policy number.

The formula you have sent works in all scenarios apart from returning
"NO" for the above policy which should be "YES".

Is this because it picks up the ASA part in the formula and is then not
over ridden by the last part? I also take it that the last "YES" is the
answer if it finds none of the above? which is how it should work.

Thanks for your help.

Bob

"Biff" wrote in message
...
Hi!

Adding this part:

If the above also ="GIG", "NO","YES"

is no problem but you need to explain further how this part fits:

Plus if cell B33="ASA123456","YES","YES"

Unless it's a separate test:

=IF(OR(INDEX(Sheet1!C:C,MATCH(B7,Sheet1!A:A,0))={" ASA","GIG"}),"NO",IF('Data
Entry'!B33="ASA12346","YES","YES"))

Biff

"dbl" wrote in message
...
Hi I am using the following formula in sheet Data Entry cell B14
=IF(INDEX(Sheet1!C:C,MATCH(B7,Sheet1!A:A,0),1)="AS A","NO","YES")
which works fine. But I need to add more to it as follows:

If the above also ="GIG", "NO","YES"
Plus if cell B33="ASA123456","YES","YES"

Cell B14 & B33 are on a sheet called Data Entry all the other info
comes from Sheet1

How do I add the above into my formula?

Bob

Hi!

Try this:

=IF(B39="ASA123456",IF(INDEX(Sheet1!G:G,MATCH(B7,S heet1!A:A,0))=0,INDEX(Sheet1!H:H,MATCH(B7,Sheet1!A :A,0)),INDEX(Sheet1!G:G,MATCH(B7,Sheet1!A:A,0))),0 )

Biff

"dbl" wrote in message
...
Biff can you help sort this one out, I need to join the 2 formulas
together


=IF(B39="ASA123456",INDEX(Sheet1!G:G,MATCH(B7,Shee t1!A:A,0))))

If the policy number =ASA123456 then return the value in G:G other wise
return 0 this works only if the policy number is True other wise it
returns false. If its false I need it to perform the following
which gives me the policy excess from G:G or H:H this part works fine.

=IF(INDEX(Sheet1!G:G,MATCH(B7,Sheet1!A:A,0),1)=0,I NDEX(Sheet1!H:H,MATCH(B7,Sheet1!A:A,0),1),INDEX(Sh eet1!G:G,MATCH(B7,Sheet1!A:A,0),1))

How do I bring the two together? I have spent hours trying but have made
no progress.

Thanks Bob


"dbl" wrote in message
...
Thanks Biff that works fine.

Regards Bob
"Biff" wrote in message
...
Hi!

Try reversing the conditions:

=IF('Data
Entry'!B33="ASA12346","YES",IF(OR(INDEX(Sheet1!C:C ,MATCH(B7,Sheet1!A:A,0))={"ASA","GIG"}),"NO","YES" ))

Biff

"dbl" wrote in message
...
Biff there is one policy number (ASA123456) which is different from the
rest

ASA is "NO" apart from the above which is "YES" so I cannot in this
case use the ASA part of the policy number.

The formula you have sent works in all scenarios apart from returning
"NO" for the above policy which should be "YES".

Is this because it picks up the ASA part in the formula and is then not
over ridden by the last part? I also take it that the last "YES" is
the answer if it finds none of the above? which is how it should work.

Thanks for your help.

Bob

"Biff" wrote in message
...
Hi!

Adding this part:

If the above also ="GIG", "NO","YES"

is no problem but you need to explain further how this part fits:

Plus if cell B33="ASA123456","YES","YES"

Unless it's a separate test:

=IF(OR(INDEX(Sheet1!C:C,MATCH(B7,Sheet1!A:A,0))={" ASA","GIG"}),"NO",IF('Data
Entry'!B33="ASA12346","YES","YES"))

Biff

"dbl" wrote in message
...
Hi I am using the following formula in sheet Data Entry cell B14
=IF(INDEX(Sheet1!C:C,MATCH(B7,Sheet1!A:A,0),1)="AS A","NO","YES")
which works fine. But I need to add more to it as follows:

If the above also ="GIG", "NO","YES"
Plus if cell B33="ASA123456","YES","YES"

Cell B14 & B33 are on a sheet called Data Entry all the other info
comes from Sheet1

How do I add the above into my formula?

Bob

Biff that only works if the ASA123456 policy number is true if its not it
only returns a 0

Sheet 1 column G is driver pays the excess column H is employer pays the
excess either can have a 0 value but when the ASA123456 policy number comes
in if the employer pays the excess it becomes a 0 even when there is a value
greeter than 0 if the driver is paying the excess it must show it. I hope
that explains it a bit better.

Regards Bob and thanks for your help its very much appreciated.
"Biff" wrote in message
...
Hi!

Try this:

=IF(B39="ASA123456",IF(INDEX(Sheet1!G:G,MATCH(B7,S heet1!A:A,0))=0,INDEX(Sheet1!H:H,MATCH(B7,Sheet1!A :A,0)),INDEX(Sheet1!G:G,MATCH(B7,Sheet1!A:A,0))),0 )

Biff

"dbl" wrote in message
...
Biff can you help sort this one out, I need to join the 2 formulas
together


=IF(B39="ASA123456",INDEX(Sheet1!G:G,MATCH(B7,Shee t1!A:A,0))))

If the policy number =ASA123456 then return the value in G:G other wise
return 0 this works only if the policy number is True other wise it
returns false. If its false I need it to perform the following
which gives me the policy excess from G:G or H:H this part works fine.

=IF(INDEX(Sheet1!G:G,MATCH(B7,Sheet1!A:A,0),1)=0,I NDEX(Sheet1!H:H,MATCH(B7,Sheet1!A:A,0),1),INDEX(Sh eet1!G:G,MATCH(B7,Sheet1!A:A,0),1))

How do I bring the two together? I have spent hours trying but have made
no progress.

Thanks Bob


"dbl" wrote in message
...
Thanks Biff that works fine.

Regards Bob
"Biff" wrote in message
...
Hi!

Try reversing the conditions:

=IF('Data
Entry'!B33="ASA12346","YES",IF(OR(INDEX(Sheet1!C:C ,MATCH(B7,Sheet1!A:A,0))={"ASA","GIG"}),"NO","YES" ))

Biff

"dbl" wrote in message
...
Biff there is one policy number (ASA123456) which is different from
the rest

ASA is "NO" apart from the above which is "YES" so I cannot in this
case use the ASA part of the policy number.

The formula you have sent works in all scenarios apart from returning
"NO" for the above policy which should be "YES".

Is this because it picks up the ASA part in the formula and is then
not over ridden by the last part? I also take it that the last "YES"
is the answer if it finds none of the above? which is how it should
work.

Thanks for your help.

Bob

"Biff" wrote in message
...
Hi!

Adding this part:

If the above also ="GIG", "NO","YES"

is no problem but you need to explain further how this part fits:

Plus if cell B33="ASA123456","YES","YES"

Unless it's a separate test:

=IF(OR(INDEX(Sheet1!C:C,MATCH(B7,Sheet1!A:A,0))={" ASA","GIG"}),"NO",IF('Data
Entry'!B33="ASA12346","YES","YES"))

Biff

"dbl" wrote in message
...
Hi I am using the following formula in sheet Data Entry cell B14
=IF(INDEX(Sheet1!C:C,MATCH(B7,Sheet1!A:A,0),1)="AS A","NO","YES")
which works fine. But I need to add more to it as follows:

If the above also ="GIG", "NO","YES"
Plus if cell B33="ASA123456","YES","YES"

Cell B14 & B33 are on a sheet called Data Entry all the other info
comes from Sheet1

How do I add the above into my formula?

Bob

Biff I don't think I explained that too well it should be:

In all cases show the value in column G if its greater than zero, if it =0
then show the value of column H if both values are 0 then return 0

Except when the policy number =ASA123456 then show the value in column G if
its 0 return 0 do not show the value of column H.

Bob



"dbl" wrote in message
...
Biff that only works if the ASA123456 policy number is true if its not it
only returns a 0

Sheet 1 column G is driver pays the excess column H is employer pays the
excess either can have a 0 value but when the ASA123456 policy number
comes in if the employer pays the excess it becomes a 0 even when there is
a value greeter than 0 if the driver is paying the excess it must show it.
I hope that explains it a bit better.

Regards Bob and thanks for your help its very much appreciated.
"Biff" wrote in message
...
Hi!

Try this:

=IF(B39="ASA123456",IF(INDEX(Sheet1!G:G,MATCH(B7,S heet1!A:A,0))=0,INDEX(Sheet1!H:H,MATCH(B7,Sheet1!A :A,0)),INDEX(Sheet1!G:G,MATCH(B7,Sheet1!A:A,0))),0 )

Biff

"dbl" wrote in message
...
Biff can you help sort this one out, I need to join the 2 formulas
together


=IF(B39="ASA123456",INDEX(Sheet1!G:G,MATCH(B7,Shee t1!A:A,0))))

If the policy number =ASA123456 then return the value in G:G other wise
return 0 this works only if the policy number is True other wise it
returns false. If its false I need it to perform the following
which gives me the policy excess from G:G or H:H this part works fine.

=IF(INDEX(Sheet1!G:G,MATCH(B7,Sheet1!A:A,0),1)=0,I NDEX(Sheet1!H:H,MATCH(B7,Sheet1!A:A,0),1),INDEX(Sh eet1!G:G,MATCH(B7,Sheet1!A:A,0),1))

How do I bring the two together? I have spent hours trying but have made
no progress.

Thanks Bob


"dbl" wrote in message
...
Thanks Biff that works fine.

Regards Bob
"Biff" wrote in message
...
Hi!

Try reversing the conditions:

=IF('Data
Entry'!B33="ASA12346","YES",IF(OR(INDEX(Sheet1!C:C ,MATCH(B7,Sheet1!A:A,0))={"ASA","GIG"}),"NO","YES" ))

Biff

"dbl" wrote in message
...
Biff there is one policy number (ASA123456) which is different from
the rest

ASA is "NO" apart from the above which is "YES" so I cannot in this
case use the ASA part of the policy number.

The formula you have sent works in all scenarios apart from returning
"NO" for the above policy which should be "YES".

Is this because it picks up the ASA part in the formula and is then
not over ridden by the last part? I also take it that the last "YES"
is the answer if it finds none of the above? which is how it should
work.

Thanks for your help.

Bob

"Biff" wrote in message
...
Hi!

Adding this part:

If the above also ="GIG", "NO","YES"

is no problem but you need to explain further how this part fits:

Plus if cell B33="ASA123456","YES","YES"

Unless it's a separate test:

=IF(OR(INDEX(Sheet1!C:C,MATCH(B7,Sheet1!A:A,0))={" ASA","GIG"}),"NO",IF('Data
Entry'!B33="ASA12346","YES","YES"))

Biff

"dbl" wrote in message
...
Hi I am using the following formula in sheet Data Entry cell B14
=IF(INDEX(Sheet1!C:C,MATCH(B7,Sheet1!A:A,0),1)="AS A","NO","YES")
which works fine. But I need to add more to it as follows:

If the above also ="GIG", "NO","YES"
Plus if cell B33="ASA123456","YES","YES"

Cell B14 & B33 are on a sheet called Data Entry all the other info
comes from Sheet1

How do I add the above into my formula?

Bob

Well, now I'm confused!

Try breaking it down and explaining it in steps:

If B39 = ASA123456

Then do this

otherwise, do this as long as B39 = ASA123456

If B39 does not equal ASA123456

Then do this

Biff

"dbl" wrote in message
...
Biff I don't think I explained that too well it should be:

In all cases show the value in column G if its greater than zero, if it =0
then show the value of column H if both values are 0 then return 0

Except when the policy number =ASA123456 then show the value in column G
if its 0 return 0 do not show the value of column H.

Bob



"dbl" wrote in message
...
Biff that only works if the ASA123456 policy number is true if its not it
only returns a 0

Sheet 1 column G is driver pays the excess column H is employer pays the
excess either can have a 0 value but when the ASA123456 policy number
comes in if the employer pays the excess it becomes a 0 even when there
is a value greeter than 0 if the driver is paying the excess it must show
it. I hope that explains it a bit better.

Regards Bob and thanks for your help its very much appreciated.
"Biff" wrote in message
...
Hi!

Try this:

=IF(B39="ASA123456",IF(INDEX(Sheet1!G:G,MATCH(B7,S heet1!A:A,0))=0,INDEX(Sheet1!H:H,MATCH(B7,Sheet1!A :A,0)),INDEX(Sheet1!G:G,MATCH(B7,Sheet1!A:A,0))),0 )

Biff

"dbl" wrote in message
...
Biff can you help sort this one out, I need to join the 2 formulas
together


=IF(B39="ASA123456",INDEX(Sheet1!G:G,MATCH(B7,Shee t1!A:A,0))))

If the policy number =ASA123456 then return the value in G:G other wise
return 0 this works only if the policy number is True other wise it
returns false. If its false I need it to perform the following
which gives me the policy excess from G:G or H:H this part works fine.

=IF(INDEX(Sheet1!G:G,MATCH(B7,Sheet1!A:A,0),1)=0,I NDEX(Sheet1!H:H,MATCH(B7,Sheet1!A:A,0),1),INDEX(Sh eet1!G:G,MATCH(B7,Sheet1!A:A,0),1))

How do I bring the two together? I have spent hours trying but have
made no progress.

Thanks Bob


"dbl" wrote in message
...
Thanks Biff that works fine.

Regards Bob
"Biff" wrote in message
...
Hi!

Try reversing the conditions:

=IF('Data
Entry'!B33="ASA12346","YES",IF(OR(INDEX(Sheet1!C:C ,MATCH(B7,Sheet1!A:A,0))={"ASA","GIG"}),"NO","YES" ))

Biff

"dbl" wrote in message
...
Biff there is one policy number (ASA123456) which is different from
the rest

ASA is "NO" apart from the above which is "YES" so I cannot in this
case use the ASA part of the policy number.

The formula you have sent works in all scenarios apart from
returning "NO" for the above policy which should be "YES".

Is this because it picks up the ASA part in the formula and is then
not over ridden by the last part? I also take it that the last
"YES" is the answer if it finds none of the above? which is how it
should work.

Thanks for your help.

Bob

"Biff" wrote in message
...
Hi!

Adding this part:

If the above also ="GIG", "NO","YES"

is no problem but you need to explain further how this part fits:

Plus if cell B33="ASA123456","YES","YES"

Unless it's a separate test:

=IF(OR(INDEX(Sheet1!C:C,MATCH(B7,Sheet1!A:A,0))={" ASA","GIG"}),"NO",IF('Data
Entry'!B33="ASA12346","YES","YES"))

Biff

"dbl" wrote in message
...
Hi I am using the following formula in sheet Data Entry cell B14
=IF(INDEX(Sheet1!C:C,MATCH(B7,Sheet1!A:A,0),1)="AS A","NO","YES")
which works fine. But I need to add more to it as follows:

If the above also ="GIG", "NO","YES"
Plus if cell B33="ASA123456","YES","YES"

Cell B14 & B33 are on a sheet called Data Entry all the other info
comes from Sheet1

How do I add the above into my formula?

Bob

If B39 = ASA123456 then take the value from Sheet1 G:G (even if this is nil
return nil). END

If B39 doesn't equal ASA123456 then take the value from Sheet1 G:G if this
is nil take the value from Sheet1 H:H END

that's it, obviously will still need to use the Index Match part so that we
pick up the correct record.

Biff I hope that makes it a lot clearer.

Thanks Bob

"Biff" wrote in message
...
Well, now I'm confused!

Try breaking it down and explaining it in steps:

If B39 = ASA123456

Then do this

otherwise, do this as long as B39 = ASA123456

If B39 does not equal ASA123456

Then do this

Biff
"dbl" wrote in message
...
Biff I don't think I explained that too well it should be:

In all cases show the value in column G if its greater than zero, if it
=0 then show the value of column H if both values are 0 then return 0

Except when the policy number =ASA123456 then show the value in column G
if its 0 return 0 do not show the value of column H.

Bob



"dbl" wrote in message
...
Biff that only works if the ASA123456 policy number is true if its not
it only returns a 0

Sheet 1 column G is driver pays the excess column H is employer pays the
excess either can have a 0 value but when the ASA123456 policy number
comes in if the employer pays the excess it becomes a 0 even when there
is a value greeter than 0 if the driver is paying the excess it must
show it. I hope that explains it a bit better.

Regards Bob and thanks for your help its very much appreciated.
"Biff" wrote in message
...
Hi!

Try this:

=IF(B39="ASA123456",IF(INDEX(Sheet1!G:G,MATCH(B7,S heet1!A:A,0))=0,INDEX(Sheet1!H:H,MATCH(B7,Sheet1!A :A,0)),INDEX(Sheet1!G:G,MATCH(B7,Sheet1!A:A,0))),0 )

Biff

"dbl" wrote in message
...
Biff can you help sort this one out, I need to join the 2 formulas
together


=IF(B39="ASA123456",INDEX(Sheet1!G:G,MATCH(B7,Shee t1!A:A,0))))

If the policy number =ASA123456 then return the value in G:G other
wise return 0 this works only if the policy number is True other wise
it returns false. If its false I need it to perform the following
which gives me the policy excess from G:G or H:H this part works fine.

=IF(INDEX(Sheet1!G:G,MATCH(B7,Sheet1!A:A,0),1)=0,I NDEX(Sheet1!H:H,MATCH(B7,Sheet1!A:A,0),1),INDEX(Sh eet1!G:G,MATCH(B7,Sheet1!A:A,0),1))

How do I bring the two together? I have spent hours trying but have
made no progress.

Thanks Bob


"dbl" wrote in message
...
Thanks Biff that works fine.

Regards Bob
"Biff" wrote in message
...
Hi!

Try reversing the conditions:

=IF('Data
Entry'!B33="ASA12346","YES",IF(OR(INDEX(Sheet1!C:C ,MATCH(B7,Sheet1!A:A,0))={"ASA","GIG"}),"NO","YES" ))

Biff

"dbl" wrote in message
...
Biff there is one policy number (ASA123456) which is different from
the rest

ASA is "NO" apart from the above which is "YES" so I cannot in this
case use the ASA part of the policy number.

The formula you have sent works in all scenarios apart from
returning "NO" for the above policy which should be "YES".

Is this because it picks up the ASA part in the formula and is then
not over ridden by the last part? I also take it that the last
"YES" is the answer if it finds none of the above? which is how it
should work.

Thanks for your help.

Bob

"Biff" wrote in message
...
Hi!

Adding this part:

If the above also ="GIG", "NO","YES"

is no problem but you need to explain further how this part fits:

Plus if cell B33="ASA123456","YES","YES"

Unless it's a separate test:

=IF(OR(INDEX(Sheet1!C:C,MATCH(B7,Sheet1!A:A,0))={" ASA","GIG"}),"NO",IF('Data
Entry'!B33="ASA12346","YES","YES"))

Biff

"dbl" wrote in message
...
Hi I am using the following formula in sheet Data Entry cell B14
=IF(INDEX(Sheet1!C:C,MATCH(B7,Sheet1!A:A,0),1)="AS A","NO","YES")
which works fine. But I need to add more to it as follows:

If the above also ="GIG", "NO","YES"
Plus if cell B33="ASA123456","YES","YES"

Cell B14 & B33 are on a sheet called Data Entry all the other
info comes from Sheet1

How do I add the above into my formula?

Bob

Ok, that makes it easier to understand!

=IF(B39="ASA123456",INDEX(Sheet1!G:G,MATCH(B7,Shee t1!A:A,0)),IF(INDEX(Sheet1!G:G,MATCH(B7,Sheet1!A:A ,0))=0,INDEX(Sheet1!H:H,MATCH(B7,Sheet1!A:A,0)),IN DEX(Sheet1!G:G,MATCH(B7,Sheet1!A:A,0))))

Biff

"dbl" wrote in message
...
If B39 = ASA123456 then take the value from Sheet1 G:G (even if this is
nil return nil). END

If B39 doesn't equal ASA123456 then take the value from Sheet1 G:G if this
is nil take the value from Sheet1 H:H END

that's it, obviously will still need to use the Index Match part so that
we pick up the correct record.

Biff I hope that makes it a lot clearer.

Thanks Bob

"Biff" wrote in message
...
Well, now I'm confused!

Try breaking it down and explaining it in steps:

If B39 = ASA123456

Then do this

otherwise, do this as long as B39 = ASA123456

If B39 does not equal ASA123456

Then do this

Biff
"dbl" wrote in message
...
Biff I don't think I explained that too well it should be:

In all cases show the value in column G if its greater than zero, if it
=0 then show the value of column H if both values are 0 then return 0

Except when the policy number =ASA123456 then show the value in column G
if its 0 return 0 do not show the value of column H.

Bob



"dbl" wrote in message
...
Biff that only works if the ASA123456 policy number is true if its not
it only returns a 0

Sheet 1 column G is driver pays the excess column H is employer pays
the excess either can have a 0 value but when the ASA123456 policy
number comes in if the employer pays the excess it becomes a 0 even
when there is a value greeter than 0 if the driver is paying the excess
it must show it. I hope that explains it a bit better.

Regards Bob and thanks for your help its very much appreciated.
"Biff" wrote in message
...
Hi!

Try this:

=IF(B39="ASA123456",IF(INDEX(Sheet1!G:G,MATCH(B7,S heet1!A:A,0))=0,INDEX(Sheet1!H:H,MATCH(B7,Sheet1!A :A,0)),INDEX(Sheet1!G:G,MATCH(B7,Sheet1!A:A,0))),0 )

Biff

"dbl" wrote in message
...
Biff can you help sort this one out, I need to join the 2 formulas
together


=IF(B39="ASA123456",INDEX(Sheet1!G:G,MATCH(B7,Shee t1!A:A,0))))

If the policy number =ASA123456 then return the value in G:G other
wise return 0 this works only if the policy number is True other
wise it returns false. If its false I need it to perform the
following
which gives me the policy excess from G:G or H:H this part works
fine.

=IF(INDEX(Sheet1!G:G,MATCH(B7,Sheet1!A:A,0),1)=0,I NDEX(Sheet1!H:H,MATCH(B7,Sheet1!A:A,0),1),INDEX(Sh eet1!G:G,MATCH(B7,Sheet1!A:A,0),1))

How do I bring the two together? I have spent hours trying but have
made no progress.

Thanks Bob


"dbl" wrote in message
...
Thanks Biff that works fine.

Regards Bob
"Biff" wrote in message
...
Hi!

Try reversing the conditions:

=IF('Data
Entry'!B33="ASA12346","YES",IF(OR(INDEX(Sheet1!C:C ,MATCH(B7,Sheet1!A:A,0))={"ASA","GIG"}),"NO","YES" ))

Biff

"dbl" wrote in message
...
Biff there is one policy number (ASA123456) which is different
from the rest

ASA is "NO" apart from the above which is "YES" so I cannot in
this case use the ASA part of the policy number.

The formula you have sent works in all scenarios apart from
returning "NO" for the above policy which should be "YES".

Is this because it picks up the ASA part in the formula and is
then not over ridden by the last part? I also take it that the
last "YES" is the answer if it finds none of the above? which is
how it should work.

Thanks for your help.

Bob

"Biff" wrote in message
...
Hi!

Adding this part:

If the above also ="GIG", "NO","YES"

is no problem but you need to explain further how this part fits:

Plus if cell B33="ASA123456","YES","YES"

Unless it's a separate test:

=IF(OR(INDEX(Sheet1!C:C,MATCH(B7,Sheet1!A:A,0))={" ASA","GIG"}),"NO",IF('Data
Entry'!B33="ASA12346","YES","YES"))

Biff

"dbl" wrote in message
...
Hi I am using the following formula in sheet Data Entry cell B14
=IF(INDEX(Sheet1!C:C,MATCH(B7,Sheet1!A:A,0),1)="AS A","NO","YES")
which works fine. But I need to add more to it as follows:

If the above also ="GIG", "NO","YES"
Plus if cell B33="ASA123456","YES","YES"

Cell B14 & B33 are on a sheet called Data Entry all the other
info comes from Sheet1

How do I add the above into my formula?

Bob

Thanks Biff that works fine, thanks for all your help it is appreciated.

Bob
"Biff" wrote in message
...
Ok, that makes it easier to understand!

=IF(B39="ASA123456",INDEX(Sheet1!G:G,MATCH(B7,Shee t1!A:A,0)),IF(INDEX(Sheet1!G:G,MATCH(B7,Sheet1!A:A ,0))=0,INDEX(Sheet1!H:H,MATCH(B7,Sheet1!A:A,0)),IN DEX(Sheet1!G:G,MATCH(B7,Sheet1!A:A,0))))

Biff

"dbl" wrote in message
...
If B39 = ASA123456 then take the value from Sheet1 G:G (even if this is
nil return nil). END

If B39 doesn't equal ASA123456 then take the value from Sheet1 G:G if
this is nil take the value from Sheet1 H:H END

that's it, obviously will still need to use the Index Match part so that
we pick up the correct record.

Biff I hope that makes it a lot clearer.

Thanks Bob

"Biff" wrote in message
...
Well, now I'm confused!

Try breaking it down and explaining it in steps:

If B39 = ASA123456

Then do this

otherwise, do this as long as B39 = ASA123456

If B39 does not equal ASA123456

Then do this

Biff
"dbl" wrote in message
...
Biff I don't think I explained that too well it should be:

In all cases show the value in column G if its greater than zero, if it
=0 then show the value of column H if both values are 0 then return 0

Except when the policy number =ASA123456 then show the value in column
G if its 0 return 0 do not show the value of column H.

Bob



"dbl" wrote in message
...
Biff that only works if the ASA123456 policy number is true if its not
it only returns a 0

Sheet 1 column G is driver pays the excess column H is employer pays
the excess either can have a 0 value but when the ASA123456 policy
number comes in if the employer pays the excess it becomes a 0 even
when there is a value greeter than 0 if the driver is paying the
excess it must show it. I hope that explains it a bit better.

Regards Bob and thanks for your help its very much appreciated.
"Biff" wrote in message
...
Hi!

Try this:

=IF(B39="ASA123456",IF(INDEX(Sheet1!G:G,MATCH(B7,S heet1!A:A,0))=0,INDEX(Sheet1!H:H,MATCH(B7,Sheet1!A :A,0)),INDEX(Sheet1!G:G,MATCH(B7,Sheet1!A:A,0))),0 )

Biff

"dbl" wrote in message
...
Biff can you help sort this one out, I need to join the 2 formulas
together


=IF(B39="ASA123456",INDEX(Sheet1!G:G,MATCH(B7,Shee t1!A:A,0))))

If the policy number =ASA123456 then return the value in G:G other
wise return 0 this works only if the policy number is True other
wise it returns false. If its false I need it to perform the
following
which gives me the policy excess from G:G or H:H this part works
fine.

=IF(INDEX(Sheet1!G:G,MATCH(B7,Sheet1!A:A,0),1)=0,I NDEX(Sheet1!H:H,MATCH(B7,Sheet1!A:A,0),1),INDEX(Sh eet1!G:G,MATCH(B7,Sheet1!A:A,0),1))

How do I bring the two together? I have spent hours trying but have
made no progress.

Thanks Bob


"dbl" wrote in message
...
Thanks Biff that works fine.

Regards Bob
"Biff" wrote in message
...
Hi!

Try reversing the conditions:

=IF('Data
Entry'!B33="ASA12346","YES",IF(OR(INDEX(Sheet1!C:C ,MATCH(B7,Sheet1!A:A,0))={"ASA","GIG"}),"NO","YES" ))

Biff

"dbl" wrote in message
...
Biff there is one policy number (ASA123456) which is different
from the rest

ASA is "NO" apart from the above which is "YES" so I cannot in
this case use the ASA part of the policy number.

The formula you have sent works in all scenarios apart from
returning "NO" for the above policy which should be "YES".

Is this because it picks up the ASA part in the formula and is
then not over ridden by the last part? I also take it that the
last "YES" is the answer if it finds none of the above? which is
how it should work.

Thanks for your help.

Bob

"Biff" wrote in message
...
Hi!

Adding this part:

If the above also ="GIG", "NO","YES"

is no problem but you need to explain further how this part
fits:

Plus if cell B33="ASA123456","YES","YES"

Unless it's a separate test:

=IF(OR(INDEX(Sheet1!C:C,MATCH(B7,Sheet1!A:A,0))={" ASA","GIG"}),"NO",IF('Data
Entry'!B33="ASA12346","YES","YES"))

Biff

"dbl" wrote in message
...
Hi I am using the following formula in sheet Data Entry cell
B14
=IF(INDEX(Sheet1!C:C,MATCH(B7,Sheet1!A:A,0),1)="AS A","NO","YES")
which works fine. But I need to add more to it as follows:

If the above also ="GIG", "NO","YES"
Plus if cell B33="ASA123456","YES","YES"

Cell B14 & B33 are on a sheet called Data Entry all the other
info comes from Sheet1

How do I add the above into my formula?

Bob

You're welcome!

Biff

"dbl" wrote in message
...
Thanks Biff that works fine, thanks for all your help it is appreciated.

Bob
"Biff" wrote in message
...
Ok, that makes it easier to understand!

=IF(B39="ASA123456",INDEX(Sheet1!G:G,MATCH(B7,Shee t1!A:A,0)),IF(INDEX(Sheet1!G:G,MATCH(B7,Sheet1!A:A ,0))=0,INDEX(Sheet1!H:H,MATCH(B7,Sheet1!A:A,0)),IN DEX(Sheet1!G:G,MATCH(B7,Sheet1!A:A,0))))

Biff

"dbl" wrote in message
...
If B39 = ASA123456 then take the value from Sheet1 G:G (even if this is
nil return nil). END

If B39 doesn't equal ASA123456 then take the value from Sheet1 G:G if
this is nil take the value from Sheet1 H:H END

that's it, obviously will still need to use the Index Match part so that
we pick up the correct record.

Biff I hope that makes it a lot clearer.

Thanks Bob

"Biff" wrote in message
...
Well, now I'm confused!

Try breaking it down and explaining it in steps:

If B39 = ASA123456

Then do this

otherwise, do this as long as B39 = ASA123456

If B39 does not equal ASA123456

Then do this

Biff
"dbl" wrote in message
...
Biff I don't think I explained that too well it should be:

In all cases show the value in column G if its greater than zero, if
it =0 then show the value of column H if both values are 0 then return
0

Except when the policy number =ASA123456 then show the value in column
G if its 0 return 0 do not show the value of column H.

Bob



"dbl" wrote in message
...
Biff that only works if the ASA123456 policy number is true if its
not it only returns a 0

Sheet 1 column G is driver pays the excess column H is employer pays
the excess either can have a 0 value but when the ASA123456 policy
number comes in if the employer pays the excess it becomes a 0 even
when there is a value greeter than 0 if the driver is paying the
excess it must show it. I hope that explains it a bit better.

Regards Bob and thanks for your help its very much appreciated.
"Biff" wrote in message
...
Hi!

Try this:

=IF(B39="ASA123456",IF(INDEX(Sheet1!G:G,MATCH(B7,S heet1!A:A,0))=0,INDEX(Sheet1!H:H,MATCH(B7,Sheet1!A :A,0)),INDEX(Sheet1!G:G,MATCH(B7,Sheet1!A:A,0))),0 )

Biff

"dbl" wrote in message
...
Biff can you help sort this one out, I need to join the 2 formulas
together


=IF(B39="ASA123456",INDEX(Sheet1!G:G,MATCH(B7,Shee t1!A:A,0))))

If the policy number =ASA123456 then return the value in G:G other
wise return 0 this works only if the policy number is True other
wise it returns false. If its false I need it to perform the
following
which gives me the policy excess from G:G or H:H this part works
fine.

=IF(INDEX(Sheet1!G:G,MATCH(B7,Sheet1!A:A,0),1)=0,I NDEX(Sheet1!H:H,MATCH(B7,Sheet1!A:A,0),1),INDEX(Sh eet1!G:G,MATCH(B7,Sheet1!A:A,0),1))

How do I bring the two together? I have spent hours trying but have
made no progress.

Thanks Bob


"dbl" wrote in message
...
Thanks Biff that works fine.

Regards Bob
"Biff" wrote in message
...
Hi!

Try reversing the conditions:

=IF('Data
Entry'!B33="ASA12346","YES",IF(OR(INDEX(Sheet1!C:C ,MATCH(B7,Sheet1!A:A,0))={"ASA","GIG"}),"NO","YES" ))

Biff

"dbl" wrote in message
...
Biff there is one policy number (ASA123456) which is different
from the rest

ASA is "NO" apart from the above which is "YES" so I cannot in
this case use the ASA part of the policy number.

The formula you have sent works in all scenarios apart from
returning "NO" for the above policy which should be "YES".

Is this because it picks up the ASA part in the formula and is
then not over ridden by the last part? I also take it that the
last "YES" is the answer if it finds none of the above? which is
how it should work.

Thanks for your help.

Bob

"Biff" wrote in message
...
Hi!

Adding this part:

If the above also ="GIG", "NO","YES"

is no problem but you need to explain further how this part
fits:

Plus if cell B33="ASA123456","YES","YES"

Unless it's a separate test:

=IF(OR(INDEX(Sheet1!C:C,MATCH(B7,Sheet1!A:A,0))={" ASA","GIG"}),"NO",IF('Data
Entry'!B33="ASA12346","YES","YES"))

Biff

"dbl" wrote in message
...
Hi I am using the following formula in sheet Data Entry cell
B14
=IF(INDEX(Sheet1!C:C,MATCH(B7,Sheet1!A:A,0),1)="AS A","NO","YES")
which works fine. But I need to add more to it as follows:

If the above also ="GIG", "NO","YES"
Plus if cell B33="ASA123456","YES","YES"

Cell B14 & B33 are on a sheet called Data Entry all the other
info comes from Sheet1

How do I add the above into my formula?

Bob