Setting up excel spredsheet...

Thanks for viewing this post i need some help on excel that i can't figure out. what i am wanting to do is create a spredsheet that when you plug in a duty for a person, a time is generated for that duty. so is tehre a way to program time for a duty that whenyou plug it into to anywhere on the spreadsheet the time pops up next to it. i have 21 employees and about 50 diff duties taht are done and want to get a time line for them. So i have there name, listed with there work hours on them also want to plug this in for calculation of time, work hours vs. Duty time, so i don't over work them. so anyways i have there name plug in the duties that i want them to complete and it alots a time for the duty, and i plug as many duties as i can for the time they are at work. Can this be done in excel, or is tehre another software taht i can use that is relativly cheap.

Thanks for the help.

I think you're surpassing the idea of a spreadsheet.
You might want to consider an actual database.

Your office suite (if it's Professional) might have come with Access.
I would look into that.

Well i was told by my boss that it could be done, i personally havent' seen it done but thought they were right. so i was just seeing if it could be done. Thanks for the info.

Well, I can't jump in and say no it can't be done... I can say that I don't know how to do it =)
Then agian, I'm not an Excel guru by any means...

You can absolutely do what you are describing in excel. If you create a table with two columns: column one contains the duty, column two contains the time duration. This can be a separate workbook, worksheet in the same workbook, or a table at the top of the existing workbook. It is incredibly flexible and can be formatted any number of ways by using the lookup functions in excel. I recommend VLOOKUP.

Create your primary spreadsheet with the employee name and other details leaving a column to enter the duty name. Your duty name should be consistent on the two tables you are creating. You will want to have a column available to put the lookup formula into so that it will update automatically.

With the cell highlighted in the column for displaying the duty duration, use the following steps to create the VLOOKUP formula.
1) On the Insert menu, select the function option, this will open the paste function wizard.
2) On the function wizard select the function category of "lookup & reference" and from the function name select "VLOOKUP". The V stands for vertical. There are options for Lookup and HLOOKUP but I have the best luck with VLOOKUP.
3) The function wizard will appear with the following four categories to be populated: Lookup_value, Table_array, Col_index_num, Range_lookup.
Lookup_value this in your example would be the cell that contains the duty name on your primary spreadsheet. Enter the cell location in the box.
Table_array this in your example is the secondary spreadsheet that contains the two columns for the duty and duration. Enter the complete cell locations for your table. For example: if your secondary table is in column A and B and is populated through row 25 you will enter the table array as $A$1:$B$25. The dollar signs in front of the cell column and cell row make the numbers static so excel does not change them when you copy the formula to another cell address.
Col_index_num this in your example would be 2 since the duration of the duty is in the second column of your table.
Range_lookup this in your example would be FALSE. Leaving the range_lookup blank or typing in TRUE will locate the closest answer to your situation. If you want an exact match to what exists on your secondary table, use FALSE.

The formula should look similar to this (However depending on way the file is setup the formula could appear slightly different):
=VLOOKUP(A4,$A$1:$B$25,2,FALSE)
or in text it would read:
If the duty on primary spreadsheet is the same as a duty on the secondary table, then enter the value in the duration column. If the duty on the primary spreadsheet does not match any of the duty entries on the secondary table show me that it does not match by putting #N/A in the cell field.

I hope this helps. You can also use the help feature to locate more assistance on the VLOOKUP Feature and what information is needed.

Skysmyles

Great tahnks, i will try to look this up soon and post if i have any questions or problems, its good to know that it can be done.

Ok i'm trying to run this post you gave me, however i'm having a problem, when i add time it keeps giving me seconds and am/pm. i've tried reseting the formula so that it reads just hours and minutes but it just keeps giving me seconds and am/pm. How do i get it to read hours and minutes and not the time of the day?

Thaks

Forgot one thing, how do i issue a time for the people, so when i plug in a duty for them it does not go over the alloted time the are working?

Bravura ~

To answer your first question, you will need to use the format option on excel to get the time to display appropriately for what you are trying to do. If you select the cells that are displaying the time by highlighting them, then go to the format menu option and select cells. You will want to select time from the list of categories, then you will have a series of types to select from to best meet your needs.

Second question is more complex. It can be done through the use of formulas or conditional formating. However, it will take more smoothing and interaction to ensure that it is displaying correctly. You can create a formula to tell you that the person is over their working hours so that you know to make a correction to the data entered.

To assist with the formula or conditional formatting, I would need to know more about how your spreadsheet is set up. If you would like to e-mail me you can send an e-mail to skysmyles@hotmail.com and I will get back to you with a more detailed formula for your specific situation.

~ Sky