If Ranges Contain One Of A Few Strings and other references

August 16, 2017 at 14:38:59
Specs: Windows 10
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


See More: If Ranges Contain One Of A Few Strings and other references

Reply ↓  Report •

#1
August 16, 2017 at 20:12:31
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.


Reply ↓  Report •

#2
August 16, 2017 at 20:38:13
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 from A136 through A167 are marked 'EQUAL'.
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 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 A167 are marked 'VARIANCE'.
c - A value of NEEDS REVIEW if NONE of the above criteria are met

Based on the requirements: A135 will only contain a numeric value variance OR zero (since it is a sum of cells). Thank you for catching that!

message edited by AJ600


Reply ↓  Report •

#3
August 17, 2017 at 04:03:00
It had been a long day and I should've stopped when I started making a ton of mistakes. I have made another update.

Reply ↓  Report •

Related Solutions

#4
August 17, 2017 at 06:43:03
re: A135 will only contain a numeric value of blank if there is no variance

I 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.


Reply ↓  Report •

#5
August 17, 2017 at 06:55:16
Zero or blank (no variance)

Reply ↓  Report •

#6
August 17, 2017 at 08:16:53
2b still says:

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

Is that correct?

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


Reply ↓  Report •

#7
August 17, 2017 at 08:24:10
A136 through A167. Think I need glasses.

----Updated 1.45 pm ET, Aug 17th.

message edited by AJ600


Reply ↓  Report •

#8
August 17, 2017 at 08:50:26
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.


Reply ↓  Report •

#9
August 17, 2017 at 10:54:45
Thank you for your patience. I would be just as annoyed if I were you.

#2 is fully updated with the request.


Reply ↓  Report •

#10
August 17, 2017 at 20:28:50

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.


Reply ↓  Report •

#11
August 18, 2017 at 07:29:45
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.


Reply ↓  Report •

#12
August 18, 2017 at 12:10:25
I got an update saying there's an update but it doesn't show me one.

Reply ↓  Report •

#13
August 18, 2017 at 12:48:18
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.


Reply ↓  Report •

#14
August 21, 2017 at 07:32:09
Thank you. If you think the possible solution may work, I could test that.

Reply ↓  Report •

#15
August 21, 2017 at 09:47:56
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.


Reply ↓  Report •

#16
August 21, 2017 at 14:04:29
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!


Reply ↓  Report •

#17
August 21, 2017 at 17:27:52
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 of VARIANCE. Otherwise EQUAL.

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 free time 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:A167

I 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


Reply ↓  Report •

Ask Question