Excel questions OR something else... - Tilted Forum Project Discussion Community

MexicanOnABike · 08-26-2008, 05:48 AM

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!

Cynthetiq · 08-26-2008, 05:54 AM

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.

healer · 08-26-2008, 06:22 AM

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:

Originally Posted by MS Excel Help

To create a list of valid entries for the drop-down list, type the entries in a single column or row without blank cells. For example: A
1 Sales
2 Finance
3 R&D
4 MIS

Note You may want to sort the data in the order that you want it to appear in the drop-down list.

Select the cell where you want the drop-down list.
On the Data tab, in the Data Tools group, click Data Validation.

The Data Validation dialog box is displayed.

Click the Settings tab.
In the Allow box, click List.
To specify the location of the list of valid entries, do one of the following:
If the list is in the current worksheet, enter a reference to your list in the Source box.

Make sure that the In-cell drop-down check box is selected.
To specify whether the cell can be left blank, select or clear the Ignore blank check box.

That's basically it. I edited the Help a bit because they always give you more info than you need. Then, to get your total for the week, just do a SUM for all your input fields and display it wherever you want.

MexicanOnABike · 08-26-2008, 06:33 AM

I have 2003 and at work I think we have 2003 as well.

I'll try what you said. I'd rather do this in excell than start over and have to make it in access. thanks!

Cynthetiq · 08-26-2008, 06:41 AM

oh I had completely forgotten about the datavalidation ability! thanks! that will come in handy for a project i'm working on.

MexicanOnABike · 08-26-2008, 05:35 PM

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....

Zweiblumen · 08-27-2008, 01:37 PM

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

MexicanOnABike · 08-28-2008, 09:45 AM

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?

Cynthetiq · 08-28-2008, 11:29 AM

I don't know nothing about macros...

How to Apply Conditional Formatting in Excel - wikiHow

MexicanOnABike · 08-28-2008, 01:10 PM

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.

Zweiblumen · 08-28-2008, 04:03 PM

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.

MexicanOnABike · 08-28-2008, 05:23 PM

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  :

repeat for 50 more names and add a few more titles for the 2nd row.

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??!

Zweiblumen · 08-29-2008, 10:25 AM

Reversing the problem and look "gaps" might be simpler. Nested If's can also be used.

ZB

MexicanOnABike · 08-29-2008, 05:24 PM

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.

Zweiblumen · 08-31-2008, 01:45 AM

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

08-26-2008, 05:48 AM	#1 (permalink)
MexicanOnABike 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! __________________ - Movie Reviews - Ratings-

08-26-2008, 05:54 AM	#2 (permalink)
Cynthetiq 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. What's the best nation in the world? DO-NATION!

08-26-2008, 06:33 AM	#4 (permalink)
MexicanOnABike Location: up north	I have 2003 and at work I think we have 2003 as well. I'll try what you said. I'd rather do this in excell than start over and have to make it in access. thanks! __________________ - Movie Reviews - Ratings-

08-26-2008, 06:41 AM	#5 (permalink)
Cynthetiq 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. What's the best nation in the world? DO-NATION!

08-26-2008, 05:35 PM	#6 (permalink)
MexicanOnABike 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.... __________________ - Movie Reviews - Ratings- Last edited by MexicanOnABike; 08-26-2008 at 06:59 PM.. Reason: Automerged Doublepost

08-27-2008, 01:37 PM	#7 (permalink)
Zweiblumen 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

08-28-2008, 09:45 AM	#8 (permalink)
MexicanOnABike 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? __________________ - Movie Reviews - Ratings- Last edited by MexicanOnABike; 08-28-2008 at 12:08 PM..

08-28-2008, 11:29 AM	#9 (permalink)
Cynthetiq Tilted Cat Head Administrator Location: Manhattan, NY	I don't know nothing about macros... How to Apply Conditional Formatting in Excel - wikiHow __________________ 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. What's the best nation in the world? DO-NATION!

08-28-2008, 01:10 PM	#10 (permalink)
MexicanOnABike 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. __________________ - Movie Reviews - Ratings- Last edited by MexicanOnABike; 08-28-2008 at 01:20 PM.. Reason: Automerged Doublepost

08-28-2008, 04:03 PM	#11 (permalink)
Zweiblumen 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

08-28-2008, 05:23 PM	#12 (permalink)
MexicanOnABike 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 : repeat for 50 more names and add a few more titles for the 2nd row. 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??! __________________ - Movie Reviews - Ratings- Last edited by MexicanOnABike; 08-28-2008 at 05:27 PM..

08-29-2008, 10:25 AM	#13 (permalink)
Zweiblumen Insane Location: at home	Reversing the problem and look "gaps" might be simpler. Nested If's can also be used. ZB __________________ Sodomy non sapiens. : I'm buggered if I know

08-29-2008, 05:24 PM	#14 (permalink)
MexicanOnABike 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. __________________ - Movie Reviews - Ratings-

08-31-2008, 01:45 AM	#15 (permalink)
Zweiblumen 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