Hello everyone,
I have a document that has all of the dates in a year on the third row of the attached Excel file.
i want a VBA script that pops-up a error message if the sum of a month exceedes a predifined value in another sheet.
So if the sum of all of the days in January for Name 1 should not exceed the value from Sheet2 C3.
This script should apply for all of the names (predifined number of row).
Document attached.
Thanks,
-Alex
I think you can do this with data validation? Select C4:BI13 and try the following data validation formula:
=SUMPRODUCT(--(MONTH($C$3:$BI$3)=MONTH(C$3)),$C4:$BI4)<=VLOOKUP(TEXT(C$3,"mmmm"),Sheet2!$B$3:$C$11,2,FALSE)
Try the attachment to see if it works:
If your problem has been solved, please use "Thread Tools" to mark the thread as "Solved".
If you're grateful for this site's existence and would like to contribute to the community, please consider posting something hilarious in our joke thread:
https://www.excelforum.com/the-water...ke-thread.html
Try this:
Please Login or Register to view this content.
Last edited by xladept; 05-23-2018 at 04:05 PM.
If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)
You can't do one thing. XLAdept
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin
Hello and sorry,
I probably wasn't clear in my initial post.
So the document will have number from 0 to 24 entered in the columns of each of the names (Name 1, Name 2 etc.).
For example i would like to enter in cell K4 the value 6 and in the L4 the value 28, if we sum up all of the values for Name 1 we will get the number 34, but in Sheet 2 we have the month of January that has a maximum number of 30, which means that the document should show a pop-up mentioning that the sum of january for Name 1 is invalid due to the fact that it exceeds the number 30.
Thanks,
-Alex
So, try this:
Please Login or Register to view this content.
I guess I'm still unclear on the goal? With the data validation approach, if you enter a 6 in K4, then attempt to enter a 28 in L4, you're blocked by the following pop-up:Originally Posted by dLhx
Isn't that what you're looking for?
Last edited by CAntosh; 05-24-2018 at 10:35 AM.
@xladept : It works perfectly. Only one question. Is there a way that the VBA script could work continuous, meaning that if a single cell is changed the scripts will run automatically?
I already gave everyone here a rep+ for helping me out with this :D
Here's event code for your Sheet1 module:
*After you paste it into your sheet1 module, you'll need to save the book as macro-enabled.Please Login or Register to view this content.
And, you're welcome and thanks for the rep!
@xladept: Man i am so sorry for asking you this but apparently i am unable to do it myself for some unknown reason or maybe i`m just too bad at understanding VBA.
I have attached the actual document that i need this script to work on (Testing Module.xlsx)
Can you please help me out with this?
Again, i`m so sorry for asking you to do this.
No attachment - but here's the book I used:
The document was too big.
Can you see it now?
See my previous post for it.
The range should be for the year .. January to December
Last edited by dLhx; 05-24-2018 at 04:28 PM.
Here's the book back:
YOU ARE A LIFE SAVER. Thank you so much!
You're welcome!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks