Hello. I had previously asked a couple questions. I have worked through those requirements but they seemed to have changed a bit- and am having trouble reverse engineering the formula.

In Cell D5 of the current sheet: 'Status' I may or may not have a field called 'Local'.

1) If Cell D5 contains 'Local' on the 'Status' sheet only:Can I get the formula to reference sheet: 'Variance'. We need:

a - A value of EQUAL only if ALL cells from A136 through A167 are marked 'EQUAL'.

b - If ANY of the cells from A136 through K167 are marked 'VARIANCE'; AND (If the number on the on the 'Variance' sheet, cell A136 is EITHER greater than the value in cell B3 on the 'Status' sheet OR is lesser than the negative of the value in cell B3 on the 'Status' sheet ONLY), We need: A value of VARIANCE. Otherwise EQUAL.

c - A value of NEEDS REVIEW if NONE of the above criteria are met

2) If Cell D5 does NOT contain 'Local' on sheet: 'Status':

Can I get the formula to reference sheet: 'Variance'. We need:

a - A value of EQUAL only if ALL cells from A136 through A167 are marked 'EQUAL'.

b - A value of VARIANCE if ANY of the cells from A136 through K167 are marked 'VARIANCE'.

c - A value of NEEDS REVIEW if NONE of the above criteria are met

Before I offer any suggestions, please verify that both 1(b) and 2(b) above are correct.

If ANY of the cells from A136 through K167 are marked 'VARIANCE'Are you really checking

A136:K167 for the string VARIANCE?re:

If the number on the on the 'Variance' sheet, cell A136 is...Based on your 1 (a, b, c) requirements it appears that it is possible for A136 to contain EQUAL, VARIANCE or a number. Is that correct?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Thank you. I have a few typos- apologies for that. 1) If Cell D5 contains 'Local' on the 'Status' sheet only:

Can I get the formula to reference sheet: 'Variance'. We need:

a - A value of EQUAL only if ALL cells fromA136throughA167are marked 'EQUAL'.

b - If ANY of the cells fromA136throughA167are marked 'VARIANCE'; AND (If the number on the on the 'Variance' sheet, cell A135 is EITHER greater than the value in cell B3 on the 'Status' sheet OR is lesser than the negative of the value in cell B3 on the 'Status' sheet ONLY), We need: A value of VARIANCE. Otherwise EQUAL.

c - A value of NEEDS REVIEW if NONE of the above criteria are met2) If Cell D5 does NOT contain 'Local' on sheet: 'Status':

Can I get the formula to reference sheet: 'Variance'. We need:

a - A value of EQUAL only if ALL cells fromA136throughA167are marked 'EQUAL'.

b - A value of VARIANCE if ANY of the cells fromA136throughA167are marked 'VARIANCE'.

c - A value of NEEDS REVIEW if NONE of the above criteria are metBased on the requirements:

A135will only contain anumeric valuevariance OR zero (since it is a sum of cells). Thank you for catching that!

message edited by AJ600

It had been a long day and I should've stopped when I started making a ton of mistakes. I have made another update.

re: A135 will only contain a numeric value of blank if there is no varianceI don't what you mean by a

numeric value.of blank

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2b still says: A value of VARIANCE if ANY of the cells from

A136 throughK167 are marked 'VARIANCE'.Is that correct?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

A136 through A167. Think I need glasses. ----Updated 1.45 pm ET, Aug 17th.

message edited by AJ600

This is getting just a bit ridiculous. You edited your post to read...

"b - A value of VARIANCE if ANY of the cells from A136 through A167 are marked 'VARIANCE'."

...yet you answered "A136 through K167".

I am just as confused as before and frankly getting a little annoyed.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Thank you for your patience. I would be just as annoyed if I were you. #2 is fully updated with the request.

Will the value in Status!B3 ever be a negative number?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

The value in B3 will never be a negative number (it will most likely be between 1 to 5 but that may vary by situation). It is the threshold of accepted variance, the result of which may be positive or negative.

I posted a possible solution then deleted it. It worked, but I'm looking for a better one.If I don't come up with one soon, I'll re-post it.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Try this. It seems to work for me. =IF(OR(

COUNTIF(A136:A167,"=EQUAL")=ROWS(A136:A167),

AND(Status!D5="Local",COUNTIF(A136:A167,"=VARIANCE")>0,

NOT(OR(A135>Status!B3,A135<-Status!B3)))),"EQUAL",

IF(COUNTIF(A136:A167,"=VARIANCE")>0,"VARIANCE",

"NEEDS REVIEW"))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Thank you. I'm not certain this works: 1) Is not satisfied when:

Status!D5 = 'Local' and if even one cell between A136 and A167 contains 'Variance' I get 'Needs Review'; whether or not the number in A135 is within or outside of the accepted variance in Status!B3.

I would expect "equal" if the variance number in A135 is within plus or minus the number in Status!B3 and Needs Review if it is not.

I really appreciate the help!

Let's recap: From your lastest version of Response #2:

1) If Cell D5 contains 'Local' on the 'Status' sheet only:

b - If ANY of the cells from A136 through A167 are marked 'VARIANCE';

AND (If the number on the on the 'Variance' sheet,cell A135 is EITHER greater than the value in cell B3 on the 'Status' sheet OR is lesser than the negative of the value in cell B3 on the 'Status' sheet ONLY), We need: A value ofOtherwise EQUAL.VARIANCE.From Response #16:

I would expect "equal" if the variance number in A135 is within plus or minus the number in Status!B3 and

Needs Review if it is not.Do you see any problem with meeting both of those requirements? I sure do. One of them indicates that you want a result of VARIANCE if Variance!A135 is not between pos/neg Status!B3, the other one indicates that you want a result of Needs Review. Sorry, you can't have both.

I can't spend any more of my

freetime trying to figure out your requirements by reading your responses in this forum. The only way that I am going to continue working on this issue is if you post a copy of your workbook at zippyshare.com and then post a link to the workbook back here in the forum.You should be able to reduce your workbook to only what is relevant to this issue. No personal information, nothing other than the 2 sheets and the 4 ranges relevant to this issue:

Status!B3

Status!D5

Variance!A135

Variance!A136:A167I would like to see real examples of data and the results that you expect based on those real examples of data.

I'd also appreciate it if you could be more "conventional" when you discuss a range refernce. Instead of using "A136 through A167", use Excel terminology: A136:A167. Instead of using, "cell B3 on the 'Status' sheet" use Status!B3. It's easier to type, easier to read and consistent with what we use in a formula.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

Ask Your Question

Weekly Poll