User inputs value into formula via an inputbox

[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,

LeapingLizardBook1.zip

[JOHN H. DAVIS]

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

Please Login or Register  to view this content.

[JOHN H. DAVIS]

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?

[LeapingLizard]

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!

[JOHN H. DAVIS]

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

[LeapingLizard]

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

[LeapingLizard]

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

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[LeapingLizard]

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

[LeapingLizard]

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.

[LeapingLizard]

Anyone with suggestions?