![]() |
![]() |
#1 (permalink) |
Location: up north
|
Excel questions OR something else...
Hi,
I'm trying to create a schedule in excel but i want it to be EXTREMELY detailed and functional. At this point, I have the DAYS on the top row(sunday to saturday) and the names on the left column. What I want to do is to save me time whenever I want to add hours in the schedule. So for example: I hire someone new and i want to give them hours on monday; well instead of just clicking in the cell and typing it out, I would like to have a drop down menu for the START time and one for the END time. so CLICK: 8 Click: 5. I can even have 2 cell per day if that's the need. Now I think I can figure this out on my own if I keep trying for a few days but this is where it gets a bit complex; I want to have a column on the right after all the days that is my total. So if I give 8 to 5 shifts to someone for 5 days a week, that should total 45hrs. 9hrs every day. So I'm not looking for you to do this all for me but maybe point me in the right direction?? And if this isn't possible in excel, what should I use? I'd like to have this on a usb key that I can bring anywhere. I know that all the computers I use at work have excel so this why I want to use it. If it would be easier in access or something, let me know!
__________________
|
![]() |
![]() |
#2 (permalink) |
Tilted Cat Head
Administrator
Location: Manhattan, NY
|
I assume since you have excel that you have access as well.
access can do what you're asking. you'll have to learn how to make the dropdown edit boxes, layout, etc. it's not too hard. there are many access resources available on the net, you may even find someone who's done it already and just has it available for download.
__________________
I don't care if you are black, white, purple, green, Chinese, Japanese, Korean, hippie, cop, bum, admin, user, English, Irish, French, Catholic, Protestant, Jewish, Buddhist, Muslim, indian, cowboy, tall, short, fat, skinny, emo, punk, mod, rocker, straight, gay, lesbian, jock, nerd, geek, Democrat, Republican, Libertarian, Independent, driver, pedestrian, or bicyclist, either you're an asshole or you're not. |
![]() |
![]() |
#3 (permalink) | ||
Found my way back
Location: South Africa
|
Excel's got a data validation feature that I think will do what you need it to. Basically, you'll put all the 'data' for the dropdown box in a column (you can hide this afterwards) and then enable data validation for all your input cells so that they look-up from your data column.
What version of Excel are you using? I'm still figuring out 2007. Once I have the exact how-to I'll post it here. -----Added 26/8/2008 at 10 : 30 : 30----- Quote:
__________________
Quote:
Last edited by healer; 08-26-2008 at 06:30 AM.. Reason: Automerged Doublepost |
||
![]() |
![]() |
#5 (permalink) |
Tilted Cat Head
Administrator
Location: Manhattan, NY
|
oh I had completely forgotten about the datavalidation ability! thanks! that will come in handy for a project i'm working on.
__________________
I don't care if you are black, white, purple, green, Chinese, Japanese, Korean, hippie, cop, bum, admin, user, English, Irish, French, Catholic, Protestant, Jewish, Buddhist, Muslim, indian, cowboy, tall, short, fat, skinny, emo, punk, mod, rocker, straight, gay, lesbian, jock, nerd, geek, Democrat, Republican, Libertarian, Independent, driver, pedestrian, or bicyclist, either you're an asshole or you're not. |
![]() |
![]() |
#6 (permalink) |
Location: up north
|
alright, I am almost done but what I need now is the same thing but for area of work.
so i have: Name: start time, end time on 1 row and on the 2nd row, i have area of work like Cash, sales, office. I gave these names a colour in the list I made but when i select them, the colour doesn't follow. Is there a way to make it so that when I pick SALES, the SALES cell and the time cell change to a different colour like red? -----Added 26/8/2008 at 10 : 59 : 47----- ok, I feel like an Idiot. I cannot figure it out. Everywhere on google, they talk about conditional formatting but this is limited to 3 per cell and I have about 10 items in my list that I want to use. If it was only for that, It would be perfect but since i'm limited, I have to look for something else to change my cell colours. Does anyone know how to do this??? IF(text = sales, cell colour = red, If text = cash, cell colour = green) << this is what I want but I can't find the right syntax or functions to do it....
__________________
Last edited by MexicanOnABike; 08-26-2008 at 06:59 PM.. Reason: Automerged Doublepost |
![]() |
![]() |
#7 (permalink) |
Insane
Location: at home
|
Here is a VBA code that you can use as a base for a macro that will do eXtendedConditionalFormatting.
Sub XconFormat() Select Case LCase(ActiveCell.Text) Case "sale" ActiveCell.Font.Color = vbRed ActiveCell.Font.Background = vbYellow Case "cash" ActiveCell.Font.Color = vbBlue ActiveCell.Font.Background = vbGreen End Select End Sub Just add Case "text" clauses as needed. Yours Zweiblumen
__________________
Sodomy non sapiens. : I'm buggered if I know |
![]() |
![]() |
#8 (permalink) |
Location: up north
|
This looks like this is what I need. can you go more into details as how to apply it ? I'm new at excel (haven't used it in over 5years).
Also, Does anyone know how to do a double ANSWER to an IF? if A equals 1, then B = something and C = something else?
__________________
Last edited by MexicanOnABike; 08-28-2008 at 12:08 PM.. |
![]() |
![]() |
#9 (permalink) |
Tilted Cat Head
Administrator
Location: Manhattan, NY
|
__________________
I don't care if you are black, white, purple, green, Chinese, Japanese, Korean, hippie, cop, bum, admin, user, English, Irish, French, Catholic, Protestant, Jewish, Buddhist, Muslim, indian, cowboy, tall, short, fat, skinny, emo, punk, mod, rocker, straight, gay, lesbian, jock, nerd, geek, Democrat, Republican, Libertarian, Independent, driver, pedestrian, or bicyclist, either you're an asshole or you're not. |
![]() |
![]() |
#10 (permalink) |
Location: up north
|
well basicly, i have too many things to use Conditional formatting. It's impossible for me to use it in this situation but it's a great tool. for now, i'm checking out what Zweiblumen said and I'm trying to use it but it's not easy.
-----Added 28/8/2008 at 05 : 20 : 05----- as far as Zweiblumen said, it works for text but not for the background... I get an error everytime I do this.
__________________
Last edited by MexicanOnABike; 08-28-2008 at 01:20 PM.. Reason: Automerged Doublepost |
![]() |
![]() |
#11 (permalink) |
Insane
Location: at home
|
Ah, you are asking about Excel IF function not the VBA.
the background for the cell is set with ActiveCell.Interior.Color = vbBlue There are many good books and websites out there on VBA (Visual Basic for Applications) programing.
__________________
Sodomy non sapiens. : I'm buggered if I know |
![]() |
![]() |
#12 (permalink) |
Location: up north
|
yes, I was able to figure it out. your answer pointed me in the right direction enough to do this.
![]() Now that I have this completed, I can now move onto the 2nd part of my problem. This one I'm really unsure of how to approach it. I have this as my worksheet: Code:
A1 b1 c1 d1 NAME : 8 : to : 17 : b2 : computer : what I want to achieve is: have a list of hours like 7 to 22 on the side and then have a code that looks at everyone with the word computer and then checks what hours they're at and this puts a marker. when the hour has been filled, it puts a check for that hour and person. when the 1st list of hours has been filled, it puts a GOOD in a cell. so it should look like: jimmy works from 8 to 17 in computers, bob works from 10 to 19 in computers and jack works 17 to 22 in computers. hours for the day are 8 to 10. with everyone listed here, Computers is GOOD. If we remove bob, the hours are still GOOD. but if we remove anyone else, it should say BAD. I then have 5 more departments to cover. Is this possible??!
__________________
Last edited by MexicanOnABike; 08-28-2008 at 05:27 PM.. |
![]() |
![]() |
#14 (permalink) |
Location: up north
|
I think I didn't explain myself good enough. I tried posting the same question on an excel forum but I didn't get an answer yet.
Get multiple answers depending on other text - Excel Help Forum If you can see it, theres also an example of what I want to achieve.
__________________
|
![]() |
![]() |
#15 (permalink) |
Insane
Location: at home
|
As you said in the excel forum it has to be done with a vba macro.
One way would be to have variables inside the macro that would present each workhour. They would all be initialised to FALSE, then the macro would start going through all the records/fields and put a true value when it finds an assignment to a workhour. When all the variables are true the macro can stop and return that this period (day ..) else it returns that there are missing assignments. Checking for each type of work seperatly would make it more readable. ZB
__________________
Sodomy non sapiens. : I'm buggered if I know |
![]() |
Tags |
excel, questions |
|
|