Basic Help needed to automate a table when filling in monthly cells.
Hello All.
I am looking for help in order to automate my summary table of data.
Attached is an Excel file with Employee costs for a bunch of Clubs. On the right Hand side is a table which summarizes ( zusammenfassung) the current (Aktuell) and Previous month(vorherige Monat)data. The Information in brackets are the german words you will see in the table.
When filling out the main table i would like the summary table on the right in each months Excel data to automatically Show ONLY the current (AKTUELLE) month data, and then Automatically put the previous months data (vorherige Monat)in the respective cell. Thus depending on what month i am filling in ONLY that month will Show up in current and the previous month in the previous month cells. Then the Difference % (unterschied)and total for the year will also subsequently Change.
Can anyone help me? I am not sure if this is possible, but if it works then it would be amazing help!.
Thanks so much all.
| Attachment | Size |
|---|---|
| Personalkosten_aller_Clubs.xlsx | 13.51 KB |
Hi, I have put formulas in
Hi,
I have put formulas in your sample sheet and attached here as well.
what I did
1) Assigned the numeric value to the months in C5:N5
2) Pull the system date by applying the today() function
3) Extract the month from Today date
5 Apply the Hlookup formula in Current column to pick the current/running month
cost of employee that is =HLOOKUP(MONTH(TODAY()),$B$5:$N$8,4,0). For the next employee/row it would be =HLOOKUP(MONTH(TODAY()),$B$5:$N$9,5,0)(its only works when you entered the cost of employee in current/running month. If you want to enter employee cost of September and if September is going on then its works fine otherwise not. I entered some of sample data into your detail table. It explain you how its works).
6 Apply the sum function in 'Previous column' and deduct the 'Current month cost' from it =SUM(C8:N8)-Q8.
Give me your email address I will forward you sample sheet with formulas.
If you have any confusion please let me know.
Akmughal
THANK YOU!
Hey akmughal,
Thank you so much. I look Forward to taking a look. I am trying to become more familier with Excel and will start courses in VBA soon, as i really think understanding Excel is important in ones Job and allows for alot more creative outcomes. My email is: JKFisher138@gmail.com
Thanks again.
JKF :)
Please have a look on sample
Please have a look on sample data sheet which I forwarded to you through email.