how t0 create formula when cell value is a result of formula

[gilnic]

Hello,
Hoping for some assistance please.I am fairly new to formulas and have been trying to create a formula with if(or...
=IF(OR(AB6=AA6,AC6=AA6),"YES","NO")

this is in cell AC1
Now the text in cells AA6,AB6,and AC6 are the result of formulas.

for example
AA6 has red AB6 has blue and AC6 has red
I want my formula to put YES in cell AC1

As I am not getting any result is it because the cells are the result of formulas and my formula cannot read the cells for the text?
I am confused because another of my formulas relating to different cells seems to work ok.
e,g,
=IF(A5=$AA$6,($AB$9),(""))

this is mainly automated so I am unable to copy and post values.

[Glenn Kennedy]

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? However, please give us an indication of the approximate number of rows of data you want the solution to work with (100, 1000, 100,000 or whatever). Please don't attach a picture of an Excel sheet (no-one will want to re-type all your stuff before starting).

1. It does NOT have to be your real sheet - mock up a SAMPLE if you need to. But not 1000's of rows!!! It makes manual checking so tedious. Whatever you do... make sure that all confidential information is removed first!!

2. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

3. Make sure that your desired solution is also shown (mock up the results manually).

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[BadlySpelledBuoy]

I may have completely misunderstood the requirement here, but if I'm correct in assumption then your formula is not returning Yes as you are not comparing AA6 with AC6 and those are the cells that match. It's nothing to do with the fact the values are derived from formulas.

Try this:

Please Login or Register  to view this content.

EDIT: If I have misunderstood then follow Glenn's suggestion above.

BSB

[gilnic]

Hello BadlySpelledBuoy,
Thank you for response.
That might be something to do with it.
I have in AA6 some text that is the result of a formula.What I am trying to do is to compare that text with the other two cells in AB6 and AC6(which are also the result of formulas)to see if there is a match and if so place yes in AC1.
If there is red in AA6 I want to check both AB6 and AC6 to see if red is in there and if so place yes in AC1.

[Glenn Kennedy]

Any chance of a sample file??

[gilnic]

Hello Glenn,
A sample is is somewhat difficult as the excel sheet is run in conjunction with gruss software.I have included a sample of the formulas that are in the particular cells of the query.

[Glenn Kennedy]

I'm not sure that we are ANY further on. There is nothing in the ranges for the formulae in row 6.... A5 to A30 is blank.... there is no sheet called selection.

[Glenn Kennedy]

The formual in AA% should be modified to:

=IFERROR(INDEX(A5:A30,MATCH(MIN(F5:F30),F5:F30,0)),"")

Asssuming the other two formula actually do work (impossible to tell without seeing the data), then wrap them in an iFERROR

=IFERROR(your_formula_here,"")

[gilnic]

Hello,
Thanks for response.The formulas in AA6,AB6 and AC6 all work correctly.
It is the cell AB4 that is of concern as it is not putting either a yes or no in the cell when the other cells are filled.

[MrShorty]

it is not putting either a yes or no in the cell when the other cells are filled.

I cannot replicate this behavior. With the "selection" sheet and other data missing, I get #N/A errors in row 6, and AB4 propagates those errors -- common behavior for Excel. If I fix up the formulas to return something random (not knowing what data you actually expect to see), I get either "Yes" or "No" in AB4, so the formula in AB4 seems to work. I certainly don't get nothing in AB4.

There must be something else in your Excel installation or in your actual worksheet that is preventing this cell from calculating, but I'm not sure how to debug it without knowing more about your scenario.

[Glenn Kennedy]

Hello,
Thanks for response.The formulas in AA6,AB6 and AC6 all work correctly.
It is the cell AB4 that is of concern as it is not putting either a yes or no in the cell when the other cells are filled.

Did you try wrapping them in IFERROR? Did that help?

If not we reallly need to see a more complete sheet!!

[gilnic]

Hello,
I have been able to solve the problem.I placed the formula in a different cell and it has worked.
I can only assume that there was something wrong with the cell that had the original formula.Thanks to all that provided help.