Printable View
Hello, I created the following formula to return either Yes or No. The return of Yes is working corrcetly. But No arguments are returning as false. I cant figure out why. Can anyone help please.
=IF(D4="M",IF(SUM('5'!$O$32:$O$42)>=5,"Yes",IF('5'!$O$32>=1,"Yes","No")))
Thanks you
Can you post a small workbook with some data that shows the issue?
Try trimming the cells you're trying to match and make sure the the format of the summed cells is correct (i.e. not text) - that can be the issue sometimes.
But like Paul said, we can help diagnose the problem quicker if you share your data set with us.
Here is a condensed version of the spreadsheet I am working with. Should be enough to get the formula to work. I am only looking to change the formula in Sheet 1, Column S.
S4 and S5 have different formulas which don't think is intentional
Attachment 21612
Try this in S4 and copy down
Code:=IF(D4="M",IF(SUM('5'!$O$32:$O$42)>=5,"Yes",IF('5'!$O$32>=1,"Yes","No")),"No")
Attachment 21613
Thanks, but still not working. I am still getting False.
I attached an updated sheet.
In my previous attachment, I am trying to change the original formula in cell S% to the one I am updating in S4. I left the original formula in the cell as it worked previously.
I gave you the formula for S4. It looks like you put it in S5??
Attachment 21614
Fixing your S5 formula and filling it into S4 ...
Attachment 21615
Seems to return the No in S4 and the Yes in S5 that I thought you were expecting
Attachment 21616
Am I failing to understand?
Here is what I am trying to do
a name is assigned either an "M" or "A" in sheet 1 column A
The sheet that I work with has 50 sheets. the sheets are assigned to an individual and are numbered according to row number of the individuals name.
for names that are assigned "M" the total of O32:O42 of their individual sheet must total at least 5 and return yes, if less than 5 must return no.
for names that are assigned "A" the total of O32 of their individual sheet must total at least 1 and return yes, if zero must return no.
My thought in creating the formula;
if the value in column D*="M" then the true argument IF(SUM('5'!$O$32:$O$42)>=5, will return "Yes"
the false argument would mean D*="A" so i used a second if argument IF('5'!$O$32>=1, to validate if true and return "Yes"
The false argument would mean that both the "M" and "A" argument is false and return "NO"
I am sorry. I meant
a name is assigned either an "M" or "A" in sheet 1 column D
The formula should follow the following sequence
Row 4: =IF(D4="M",IF(SUM('4'!$O$32:$O$42)>=5,"Yes",IF('4'!$O$32>=1,"Yes","No")))
Row 5: =IF(D5="M",IF(SUM('5'!$O$32:$O$42)>=5,"Yes",IF('5'!$O$32>=1,"Yes","No")))
Row 6: =IF(D6="M",IF(SUM('6'!$O$32:$O$42)>=5,"Yes",IF('6'!$O$32>=1,"Yes","No")))
etc (will follow sequence to row 50.)
Ok, then try this
I think you were getting FALSE instead of the expected "No" because the first IF didn't have a value for a false condition, and so it defaulted to just False
Code:
Row 4: =IF(D4="M",IF(SUM('4'!$O$32:$O$42)>=5,"Yes",IF('4'!$O$32>=1,"Yes","No")), "No")
Row 5: =IF(D5="M",IF(SUM('5'!$O$32:$O$42)>=5,"Yes",IF('5'!$O$32>=1,"Yes","No")), "No")
Row 6: =IF(D6="M",IF(SUM('6'!$O$32:$O$42)>=5,"Yes",IF('6'!$O$32>=1,"Yes","No")), "No")
I'd suggest that you look into using INDIRECT() and ROW() to avoid a lot of typing where the only different in 46 rows it the referenced sheet name
getting close, no longer getting FALSE.
Formula is working correctly for the "M", but not the "A" I should be getting "yes" with a value of 1, but am getting No
I really do appreciate your help.
Attachment 21617
How do you us using INDIRECT() and ROW() I have never used them prior
Put this in S4 and fill down
Online help has pretty good explanationCode:=IF(INDIRECT("D"&ROW())="M",IF(SUM(INDIRECT("'"&ROW()&"'!$O$32:$O$42"))>=5,"Yes",IF(INDIRECT("'"&ROW()&"'!$O$32")>=1,"Yes","No")),"No")
I am still getting "NO" when D*=A and O32=1, should result as "yes"
I see how indirect works now.. Thanks for the tip!
Back to the beginningQuote:
Originally Posted by boccuz
I assume that this should really be to worksheet "4"
In pseudocode, is this the correct logicCode:=IF(D4="M",IF(SUM('4'!$O$32:$O$42)>=5,"Yes",IF('4'!$O$32>=1,"Yes","No")))
D4 = "A" sCode:If D4 = "M" Then
if SUM('4'!$O$32:$O$42)>=5 Then
S4 = "Yes"
ElseIf '4'!$O$32>=1 Then
S4 = "Yes"
Else
S4 = "No"
End IF
Else
S4 = "No"
EndIf
Yes, this would be for sheet 4 =IF(D4="M",IF(SUM('4'!$O$32:$O$42)>=5,"Yes",IF('4'!$O$32>=1,"Yes","No"))) and return argument in S4 (D4=A)
this would be for sheet 5 =IF(D5="M",IF(SUM('5'!$O$32:$O$42)>=5,"Yes",IF('5'!$O$32>=1,"Yes","No"))) and return argument in S5 (D5=M)
Yes, in pseudocode, that is the correct logic.
S4 should be yes when Sheet for O32 >=1
S4 should be yes when Sheet 4, O32 >=1, but is returning No.
That doesn't agree with the pseudocode in #15 that you said was correctQuote:
Originally Posted by boccuz
Since D4 is NOT = "M" the final Else returns "No"
I'm guessing that there are different checks for M's and A's????Code:If D4 = "M" Then <<<<<<<<<<<<<<<<<<<<<<<<<<<<<< if not "M"
if SUM('4'!$O$32:$O$42)>=5 Then
S4 = "Yes"
ElseIf '4'!$O$32>=1 Then
S4 = "Yes"
Else
S4 = "No"
End IF
Else <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< goes here
S4 = "No"
EndIf
Is this any closer to the logic?
Code:If D4 = "M" Then
if SUM('4'!$O$32:$O$42)>=5 Then
S4 = "Yes"
Else
S4 = "No"
Endif
Elseif D4 = "A" Then
If '4'!$O$32>=1 Then
S4 = "Yes"
Else
S4 = "No"
End IF
Else
S4 = "No"
EndIf
I'd use a User Defined Function since I (personally) don't like to use long complicated worksheet formulas
Code:Option Explicit
Function EarnedMinimum() As Variant
Dim ws As Worksheet
Dim r As Long
Dim rng As Range
Application.Volatile
r = Application.Caller.Row
Set ws = Worksheets(CStr(r))
Set rng = Application.Caller.Parent.Rows(r)
If Len(rng.Cells(4).Value) = 0 Then
EarnedMinimum = vbNullString
ElseIf rng.Cells(4).Value = "M" Then
If Application.WorksheetFunction.Sum(Worksheets(CStr(r)).Range("$O$32:$O$42")) >= 5 Then
EarnedMinimum = "Yes"
Else
EarnedMinimum = "No"
End If
ElseIf rng.Cells(4).Value = "A" Then
If Worksheets(CStr(r)).Range("$O$32").Value >= 1 Then
EarnedMinimum = "Yes"
Else
EarnedMinimum = "No"
End If
Else
EarnedMinimum = CVErr(xlErrValue)
End If
End Function
wow.. Thanks. So I just copy the cell down to row 50?
Should be all that's neededQuote:
Originally Posted by boccuz
The important thing is Application.Caller witch is the cell that the formula is in
Once we have that, we know the .Row and that allows us to go to the correct worksheet
If that works out, I noticed that it looks like you could have 100 buttons in A and B on "1", with the only difference being the virtually identical 1 of 100 macros
Maybe using a worksheet event hander to process a double click in A4, B4, A5, B5, .... would be easier
I accidently deleted the "Home" button macroCode:Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim r As Long, c As Long
With Target.Cells(1, 1)
r = .Row
c = .Column
End With
If r < 4 Or r > 50 Then Exit Sub
If Len(Me.Cells(r, 4).Value) = 0 Then Exit Sub
If c = 1 Then
Call Application.Goto(Worksheets(CStr(r)).Range("A49"), True)
ElseIf c = 2 Then
Call Application.Goto(Worksheets(CStr(r)).Range("A75"), True)
End If
End Sub
don't think I am copying the code correctly. I cannot get the formula to work in my working spreadsheet. I get ###### as a result.
I am going to Developer > Visual Basic > Module1_UDF > View Code then copy the code inside the box. not sure about about "Option Explicit" I don't see that. what am I missing?
actually, I am not seeing a Module1_UDF in my working spreadsheet. Only see it when your attachment is opened.
a lot of those macros are broken. I tried deleting, but couldn't. the delete button was grayed out.
I inserted a new Standard Module, named it Module1_UDF, and wrote the macro on thatQuote:
Originally Posted by boccuz
Attachment 21625
Don't know what i am doing wrong. I copied in my working spreadsheet and am getting false regardless of argument. I did the same test in my sample spreadsheet and it worked. copied to both spreadsheet exactly the same
would you be willing to meet with me over skype and I will share my screen to show you what i am seeing? The file size of the working spreadsheet is too large for me to attach.
easier to just attach a workbook with "1", "4", and "5" again to the forum
Include the version of the macro you're having issues with
It works with ver 3 -- basically the same as ver 2 --
Do you have calculation set to automatic?
Here is the sample spreadsheet.
I copied to earnedmimimum code to module 1.
Attachment 21628
yes, calculations is set to auto.
When you put the function in, you didn't remove the formula, so it was probably testing something like (formula) = (function) as a Boolean, and since they didn't match the answer was False
Attachment 21631
Try ver 5
jeez... thanks. working now. I truly appreciate your help on this. Thank you very much!