I am attempting to use a combination of "IF" and "IS" statments to solve for
multiple scenarios. For example, I need the following to be solved with the
IF statement:

1) If a price is changed but the item number stays the same, then give me
the supplier that is in cell AJ7
2) If no change is made, then give me the supplier that is in cell C7
3) If a new item number has been entered to replace a deleted item number,
show me the supplier that is in cell C7
4) Delete item with no replacement

I have tried the following to solve and I can only get 2 out of the 4 issues
solved.

=IF(istext(AJ7),C7,AJ7) - solves for (1) and (2)
=IF(istext(C7)AJj7,C7) - solves for (1) and (3)
=IF(isblank(C7),AJ7,C7) - solves for (1) and (2)
I have used (isnontext), and I have tried AND/OR statements but to no avail.


The problem is due to the fact that one of my reference cells (AJ) has a
vlookup formula that will either give me data if the criteria is true, or it
will give me no data (the cell will be blank) if the criteria is false. When
no data shows up, excel looks at the cell as "not empty" even though no data
appears.

I need help........
--
Debbie Sherman

Debbie

apologies that this is not more complete solution but I dont have time to
repsond in detail.

On your last point - the 'isblank' function rerturning false even though the
vlookup is returning nothing - can be solved by comparing the cell to ""
rather than using 'Isblank' - for example:

= or(isblank(a1),a1="")

hope this helps a little

Reg

"Debbie" wrote:

I am attempting to use a combination of "IF" and "IS" statments to solve for
multiple scenarios. For example, I need the following to be solved with the
IF statement:

1) If a price is changed but the item number stays the same, then give me
the supplier that is in cell AJ7
2) If no change is made, then give me the supplier that is in cell C7
3) If a new item number has been entered to replace a deleted item number,
show me the supplier that is in cell C7
4) Delete item with no replacement

I have tried the following to solve and I can only get 2 out of the 4 issues
solved.

=IF(istext(AJ7),C7,AJ7) - solves for (1) and (2)
=IF(istext(C7)AJj7,C7) - solves for (1) and (3)
=IF(isblank(C7),AJ7,C7) - solves for (1) and (2)
I have used (isnontext), and I have tried AND/OR statements but to no avail.


The problem is due to the fact that one of my reference cells (AJ) has a
vlookup formula that will either give me data if the criteria is true, or it
will give me no data (the cell will be blank) if the criteria is false. When
no data shows up, excel looks at the cell as "not empty" even though no data
appears.

I need help........
--
Debbie Sherman

Thanks for your response. After stepping away from it for a while I realized
I was making it too difficult. I was able to solve the issue by using the
following:

IF($AJ181="",C181,AJ181)

It solved all four of the issues.
--
Debbie Sherman


"Debbie" wrote:

I am attempting to use a combination of "IF" and "IS" statments to solve for
multiple scenarios. For example, I need the following to be solved with the
IF statement:

1) If a price is changed but the item number stays the same, then give me
the supplier that is in cell AJ7
2) If no change is made, then give me the supplier that is in cell C7
3) If a new item number has been entered to replace a deleted item number,
show me the supplier that is in cell C7
4) Delete item with no replacement

I have tried the following to solve and I can only get 2 out of the 4 issues
solved.

=IF(istext(AJ7),C7,AJ7) - solves for (1) and (2)
=IF(istext(C7)AJj7,C7) - solves for (1) and (3)
=IF(isblank(C7),AJ7,C7) - solves for (1) and (2)
I have used (isnontext), and I have tried AND/OR statements but to no avail.


The problem is due to the fact that one of my reference cells (AJ) has a
vlookup formula that will either give me data if the criteria is true, or it
will give me no data (the cell will be blank) if the criteria is false. When
no data shows up, excel looks at the cell as "not empty" even though no data
appears.

I need help........
--
Debbie Sherman