User inputs value into formula via an inputbox

Hello,

I don't know if this can be done, but here goes. I have a formula; =SUMPRODUCT((E2:E2500)*(C2:C2500="Bath Blanket")*(INT(A2:A2500)=DATE(2012,11,15))). I would like to ask the user for values to input into the formula shown above.

For instance; =SUMPRODUCT((E2:E2500)*(C2:C2500="User input")*(INT(A2:A2500)=DATE([COLOR="#0000CD"]User inputs this date in this format 2012,11,15)))

Is there someway to allow the user to input values into formulas via inputboxs? I.E. C2:C2500 = " User input value" and the same holds true for the date in the original formula INT(A2:A2500="User inputs date in same format ")

What I am trying to do is extrapolate out a value out of using two different criteria to reference that value if that makes sense, so ......
in the attached workbook I would like to be able to input two values; "Pillowcases" for the data of "10/1/2012" and then get a total count of 175 pillowcases for the date of 10/1/2012.

I could probably use a pivot sheet, but am not at all comfortable with them. I would rather not change the worksheet in anyway if I can get away with it.

So the macro would do: What's the Item Description? Answer "Pillowcases" Secondly; What's the Date? "2012, 10, 1" and the Answer is 175.

Thanks for any suggestions or direction with this,

LeapingLizardBook1.zip

This gives 225? But the formula reflects what you want is there something missing?

Sub LeapingLizard()
Dim x As String
Dim y As String

x = InputBox("Please Enter The Item Description")
y = InputBox("Please Enter the Date ie. YYYY,MM,DD")

Range("I2").Formula = "=SUMPRODUCT((E2:E2500)*(C2:C2500=""" & x & """)*(INT(A2:A2500)=DATE(" & y & ")))"

MsgBox Range("I2").Value

End Sub

Originally Posted by LeapingLizard

Hello,

I don't know if this can be done, but here goes. I have a formula; =SUMPRODUCT((E2:E2500)*(C2:C2500="Bath Blanket")*(INT(A2:A2500)=DATE(2012,11,15))). I would like to ask the user for values to input into the formula shown above.

For instance; =SUMPRODUCT((E2:E2500)*(C2:C2500="User input")*(INT(A2:A2500)=DATE([COLOR="#0000CD"]User inputs this date in this format 2012,11,15)))

Is there someway to allow the user to input values into formulas via inputboxs? I.E. C2:C2500 = " User input value" and the same holds true for the date in the original formula INT(A2:A2500="User inputs date in same format ")

What I am trying to do is extrapolate out a value out of using two different criteria to reference that value if that makes sense, so ......
in the attached workbook I would like to be able to input two values; "Pillowcases" for the data of "10/1/2012" and then get a total count of 175 pillowcases for the date of 10/1/2012.

I could probably use a pivot sheet, but am not at all comfortable with them. I would rather not change the worksheet in anyway if I can get away with it.

So the macro would do: What's the Item Description? Answer "Pillowcases" Secondly; What's the Date? "2012, 10, 1" and the Answer is 175.

Thanks for any suggestions or direction with this,

LeapingLizardAttachment 195387

Based on the sample you provided I think the answer is 225?

Thanks John I certainly appreciate the quick response. I will get back to you on this when I get a chance to try it. Thanks!

You're welcome. I hope it works out for you.

How do I state for the MsgBox If I want it to read the title of the message box says, The answer is: 225 and Do you want to enter another?

Thanks

Here is what I tried, but it gave me an syntax error.
MsgBox("Would you like to try another?", vbYesNo, , "The Answer Is: & Range("I2").Value &")

Trying to get the message box title to be; The Answer is: Then the actually answer followed by Do you what to enter another? Yes/No
Thanks
LeapingLizard

Maybe:

Sub LeapingLizard()
Dim x As String
Dim y As String
Dim z As String
Dim u As String

g:
x = InputBox("Please Enter The Item Description")
y = InputBox("Please Enter the Date ie. YYYY,MM,DD")

Range("I2").Formula = "=SUMPRODUCT((E2:E2500)*(C2:C2500=""" & x & """)*(INT(A2:A2500)=DATE(" & y & ")))"

u = "The answer is: " & Range("I2").Value & " Do you want to enter another?"

z = MsgBox(u, vbYesNo, "MAKE A CHOICE")

If z = vbNo Then

    Exit Sub
    
Else

    GoTo g
    
End If


End Sub

Forgive me if this posted it did not seem that way. If I want to add a dropdown list from a hidden sheet called LookupLists in cells b2:b36. How can this be accomplished?

Thanks

Let me explain more; The code John provided does the job, but I just want to dig a little deeper by adding a dropdown list to input the item description from a hidden sheet called LookupLists in range b2:b36. And is there a way instead of putting one date in give a range of dates to look in to extrapolate the number of items ordered.
I have used the code John suggested above in this thread and an example of the file is included above also.

Thanks and Happy Thanksgiving to you all.

Anyone with suggestions?