Hello,
My project is nearly finished but i still need your help for some details, some things i feel like i didnt solve clean enough.
i dont wanna hear that "this is impossible" there is even a function that changes colors of cells involved and it works, the rest should be even easier
This is gonna be one of those posts that you hate for being too complicated, long and badly explained, but im trying my best.
Heres a short desribtion of what it does, you really should try it yourself though, its kinda intuitive. I tried to change as much to english as possible and added desribtions here and there
SETTING tab offers you names of workes and their color (you can change both) price list of services and times and other things you can change, the entire sheet "1" takes its data from here, so i can later copy it and create a whole month (from 1 to 31)
in sheet "1" there is a time Schedule where you can clearly and with colors assign and see who works on what for how long (currently thai massages)
from the time and service a price from the list is found and later added to accounting tab on the right, different types of payment are possible and counted immediately
choosing a name and a time changes the colors area and color (the source for that are blue boxes on the rightmost place, look at that function LOOK AT IT! Im sure there was a better solution
thing i need help with:
1)
SUMIF(S) doesnt seem to work with multiple cells that arent touching each other - i need to sum times of 0,5 - 2 hours depending on either the cells colors or the workers name next to it + only if a checkbox "paid" is checked (though i also need to know those without checking)
since i was going for a simple design the cells arent perfect for this and sumif doesnt seem to get the structure, or i dont know how to type it since it always thinks im about to type a new variable instead of making the cell range bigger...
the color suming function i added had the same problém
i wanted to at least count the colored cells and by counting them i could have the time but again because of the design that didnt work either.
i guess another table liek those three on the right would be necesary, but i already didnt like those three, there must be a better solutions than typing all those formulas...
2) if 1) is solved, the numbers shoulder appear in the black brackets under the workers names, then i would use conditional formating to color them depending on who has the least and the most, but would it maybe be possible to move that data and names to a small table to put next to the time Schedule, where they would continuously switch positions to be ordered by most or least hours? something like a filter function on ebay
3)the colors after chosing longer time and changing it to a shorter one dont dissapear, the functions can be refreshed (see refresh button top left) but nothing deletes the color, is it possible to add a line to the button that would delete the color of the timeschedule only before the ctrl+alt+F9 button refresh? maybe that would solve the problém, not automatic, but the simplest solution
4)I would like to have the current time somehow visible - as in, maybe conditional formatting selecting with a red color the times with something like "=(time)" im not sure how, thats why i ask
5)is it even possible for something like this to be created in excel and actually use it? maybe acces is better for that, i dont really understand what acces does but i remember making a simple application in it at school, but i think it only uses excel entires.. idk..
i would like this with the functions i put together to be a fully functional calendar with months and days and each day with this neat ordered Schedule, possibly later somehow sharable online in real time so all workes have an update when somethings changed and customers see if were busy (some kind of view mode) - i take any ideas that might work
6) a way to keep user from assigning two workers to the same time twice (either by keeping him from coloring two rows the same color, thats the simplest method i could think of but didnt get it to work, or by keeping two names out of eachother, but then you would have to count in the times as well and its complicated) it would possibly be enough to just use the cells for customers names for this task, i think)
i probably forgot half of the things i wanted but here are some bugs i found
1)it crashes, often.
2) sometimes when i play in the settings tab suddenly the colors get messed up and half the tab changes colors, i didnt find any forgotten function or anything and have no idea what causes it
this project is too cool to give up on and its gonna save me a lot work of counting and paying attention.
id like to keep the design this way but if someone makes it better and fully functional the same way, why not use it.
Thanks and i hope we can create something this great together so people can use it to make their lifes easier.
Bookmarks