I know. You see employees keep changing what they want but NOT anymore. Thank you for the loggin form! I will try if I can manage to do the sum, otherwise I can do it by hand ? thank you, you are very helpfull !
Posts by pantakos
I really understand. The plan will not change from now on, and I will stick to what is the first plan
1. To create a calendar that will create individual day sheets - done! Thanks to your help
2. To sum monthly all days , specific cells -done! partly not monthly per day but per year. Thank you again. If it can be done monthly then please help.
3. Users etc for logging I will find the way.
Anyway , thank you for your effort and advices
Yes... that is the plan, You are correct. I know that this will be heavy excel and difficult to manage. But, they want it like that. Maybe, I will transfer all sheets of a month to a new worksheet, in order to make it lighter. But, I have to go as they want.
The structure I post at #53 can be done?
Thank you for the advice and effort.
royUK I mentioned it at post #53 .But its ok, you have done a lot of work.
Total of all sheets yes, but not by month that I need. Ideal will be if I choose i.e. JUNE at calendar tab to bring also (at new sheet or otherwise) the totals for the current month. You see this excel will be used for a Cafe-Bar for the cashier , that is why needed the calendar in order to break down by day the cashier. An an the end of the month to calculate the total of it.
The users, yes I mentioned them later (you see my users are changing thoughts and ideas, as the cafe-bar is new but now this is the final structure of the excel file)
I thought that was clear, but as always, my English are not that good, I can think and talk what my thoughts are, but not write them ...
I am sorry I wasn't clear
Thank you !
Thank you royUK !
Will this sum all the days of a month (for each cell ie B15, C15, D15, E15, F15) ? I need the sum (monthly).
Will this continue to add forever (all months in total) or it will change calculation when month change?
But yes, it is wonderful as it is, very clever I have to say
About the login? Can I use cell protection or not working with various users? Or I need a login form to track login?
btw fantastic resume ! well done
The running totals or new sheet or under calendar, it doesn't matter really, as long they are there.
I just found it and post it.
royUK Thank you !!!
if the data should be at one sheet or a totally different excel I don't care, as long as it works.
You see I, as I am trying to catch users, my colleagues need also a login / logout form to know when a user enters and leaves excel, users from a another excel or sheet. So need this just to check when someone enters excel to do changes ( to track who or did what). They said that this will be the last change... I know that I asks TOO MUCH but as you can understand you are my only hope. I really appreciate all the help and effort royUK , I promise I will not bother you again ...
So the final structure will be
1) Login form (as described above)
2) Calendar to make day choice (ok that, thank you)
3) Sum for all days of a month for specific cells (per month)
too much huh?
Yes you are absolutely right. Unfortunately, at my company all the users asks for something else, and additions. That's why sometimes I really trying to catch up with them.
What the other approach will be?
If you can, help me to be better excel person ..
I am not a master in excel (you are) but I am trying to do my best and keep searching all the time.
Really thank you so much royUK
I have found this vba in inernet , it sum Monthly and Yearly , but not from collecting data from workrksheets but from one sheet.Code
Sub Sum_by_Month_and_Year() 'declare variables Dim ws As Worksheet Dim output As Range Set ws = Worksheets("Analysis") Set output = ws.Range("F8") monthvalue = ws.Range("C5") yearvalue = ws.Range("C6") counter = 0 'sum value by month and year For x = 9 To 15 If Month(ws.Range("B" & x)) = monthvalue And Year(ws.Range("B" & x)) = yearvalue Then counter = counter + ws.Range("C" & x) End If Next x output = counter End Sub
Can this be modified for my case?
Or can I use sumproduct or sumifs? and not use vba?
Thank you in advance!
royUK thank you (again) for your reply
You can see at the file attached what I mean about the total.
At sheet 26-06-2021 you can see some cell colored in RED
These are the totals of that day.
I want to sum the total of every day of the month (monthly sum) and display it at the Calendar sheet when I choose that Month (ie June for now)
At sheet calendar you can see where I need to put the monthly sum (yellow)
I really hope that was clear!
royUK what do you mean?
Every month at one sheer? or all the totals at one sheet ?
I think that I didnt tell the problem correct.
Can I sum specific cell at the table sheet for the end of every month? I need at the last day to sum the all the totals from all the days of the current month.
Can you help?
So it must be difficult . Thanks for your time. If you can give me a hint what to do, because I am not good with excel, let me know.
Thank you royUK !!!
royUK Thank you for your reply.
What I'd like to do is the when the new tab (the one that made under the today()) is filled (ranges B3:E5 & B10:E12) and change tab color from yellow to red, also the same date (today()) of the calendar to be also red (not yellow) in order to understand that that day is completed. My English are not that good, excuse me...
The second part of my question, is that I need the sum of every month of the calendar for B15, C15, D15 and F15 (something like the total of every month). I searched and I can use the ie SUM(Firstsheert:lastsheet!B15) but I cant make it work fom the current month (the current one). As you see from the picture at Calendar Dynamic Color Cell - VBA post. That part will be at table tab.
I really hope you understand ...
royUK I know that I ask too much. Can the above question Calendar Dynamic Color Cell - VBA be done?
And, can we have the sum of each month? The totals are
ΣΥΝΟΛΑ 1 0 0 0 0
from table tab. B1, C1,D1,F1 (separate not the sum of them) , but the total to be under the month at the calendar per month. If I made it clear.
Thank you, you saved me
if I ask (if it can be) one very very last help, is it possible?
Is it possible when tab goes red also the date of the calendar be red and stay red and the next day? Something like, "ok that day is complete"
If not possible, its ok I understand, anyway your help is more than expected!
I did something stupid! I copied a part of code, so not working
I copied all code and everything is working perfect !!!
I am ok, thank you very very much for your effort and patience!
Oh.. the code is actually working but only If I close and open file, not on the fly as the previous one. So ignore works.