Planning training in Excel

I have been tasked to organise training for my company and they have
asked me to use MS Project, which has totally blown my mind.  Having
done some investigating Project will not give me the answers that I
need surprisingly, but I am hoping Excel will

I am really trying hard to figure a way of schedulign training for 200
individuals, with 20 different modules (fixed dates) and take into
account holidays. So for instance Joe Bloggs needs to attend Module A,
B & D over the next 3 months, but he also has 2 weeks holiday booked.
There are four other people in his department but I can only allow two
people at any one time to attend.  It sounds like one of those Logic
Problems, and thats where I fall seriously short.  I am drawing a
balnk.  I was hoping someon on this group could give me some pointers
on what to do as I am rubbish with formulas etcor know where I can
download a template which may help me out here?  I know what I want as
an end result but not sure which order I need to imput it into Project,
please help? 


Thanks - Scarlet

0
7/6/2005 8:43:48 AM
excel 39879 articles. 2 followers. Follow

2 Replies
421 Views

Similar Articles

[PageSpeed] 26

scarlet wrote...
>I have been tasked to organise training for my company and they have
>asked me to use MS Project, which has totally blown my mind.  Having
>done some investigating Project will not give me the answers that I
>need surprisingly, but I am hoping Excel will
....

Spreadsheets are not particularly good at slotting multiple individuals
into multiple classes on different dates. It can be used to do this,
but only in the same way as using paper & pencil. A database like
Access would be far better suited to this sort of task.

That said, you need to find out whether you can use anything other than
Project. If not, you're wasting time. While it may be easier for you to
use something else (and, IMO, Access would be better for this than
Project or Excel), your boss likely wants something that s/he or
someone else could follow if you were hit by the proverbial bus.

If you insist on using Excel, at least follow an Access-like,
table-driven approach. The starting point is creating a table of
available dates, which could be done as simply as entering Date in cell
A1 of one worksheet, then =TODAY() in A2, =A2+1 in A3, filling A3 down
as far a needed (perhaps through 12/31/2006 - about 18 months from
now), then selecting all of col A, Edit > Copy, Edit > Paste Special as
values on top of itself to make the dates constants. Then add formulas
like

B2:
=TEXT(A2,"ddd")

and fill down to match up with the dates in col A, then autofilter cols
A and B, setting a Custom Filter for col B to begins with S to filter
out Saturdays and Sundays, then select the filtered cells and delete
them (not clear them, so Edit > Delete, not Edit > Clear). Then remove
the autofilter. Delete col B and go through col A deleting holidays.
You'll be left with a table of available course dates.

Next, create a course table. If there are 20 different units, and each
unit were offerred, say, on 6 different dates, Enter Unit in cell B1 of
another worksheet, enter Unit01 in B2:B7, Unit02 in B8:B13, etc., then
enter Date in C1, and enter course dates in col C beginning in cell C2.
I've left col A for a key field. Enter UnitKey in cell A1, then

A2:
=B2&"."&IF(B2=B1,RIGHT(A1,1)+1,1)

Fill A2 down to match up with the Unit entries in col B. Use Edit >
Copy, Edit > Paste Special as values to convert col A to values. Add a
4th field, with Location in cell D1 and location IDs in col D beginning
with cell D2.

Next, create a location table with location ID in col A and location
description in col B. The location IDs in col A could be as simple as

A2:
=TEXT(ROW()-1,"\L000")

filled down then converted to constants. Use those location IDs in

Next, create an employee table with employee ID field in col A and
employee name in col B.

Finally, create an enrollment table in another worksheet. This table
would have a composite key. Col B would hold employee IDs, col C unit
keys, and col A would hold the combined key. Each combination of
employee ID and unit key should differ from all the others. Enter
EnrollKey in A1, EmployeeID in B1, UnitKey in C1. Each employee would
need to appear 20 times in col B. The EnrollmentKey would be generated
by formulas like

A2:
=B2&"_"&LEFT(C2,4)

At this point you could supplement several of these tables with
additional calculated fields. For example, add a column to the Unit
table in col E giving the number of employees scheduled for each
section of each unit using formulas like

E2:
=COUNTIF(INDEX(EnrollmentTable,0,2),A2)

and add 20 fields to the employee table giving the scheduled course
dates for each employee. If you use only col B for employee name, then
with Unit01 in C2, Unit02 in D1, etc.,

C2 [array formula]:
=VLOOKUP(VLOOKUP($A2&C$1,EnrollmentTable,3),UnitTable,3)

which would pull the dates each employee would be taking each unit.

0
hrlngrv (1990)
7/6/2005 6:32:42 PM
Wow that looks fantastic.  I have printed this off and will work on it
at the weekend.  I know the pain is going to be from the start but once
the sheet is set up it shoul dbe fine.  At the moment I am doing it all
manually and for each individual and its taking me days to achieve
this, and when someone comes along with a reason for their inability to
attend it causes mayhem.  Thank you ever so much, I really do
appreciate it.  I can now walk back from the cliff edge, I think!!! :-D
  If I do get a little stuck can I ask for your assistance again?

0
7/7/2005 6:40:24 AM
Reply:

Similar Artilces:

why are the shadow colors grayed out in excel 2003
I added a shadow to a block of cells. I tried to change the color of the shadow as the help instructed. The colors are grayed out. Was the shape still selected, when you tried to select a Shadow colour? JRose wrote: > I added a shadow to a block of cells. I tried to change the color of the > shadow as the help instructed. The colors are grayed out. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html ...

how to use Excel 2007 Sensitivity Analysis
could any help provide a sample excel worksheet with sensitivity analysis demo, including use of scenario manager to generate scenario summary. ...

Excel file with Active X programming growing uncontrollably
I have designed an Excel spreadsheet with a number of formulas, buttons (Active X) and VB code connected to them. All it does is calculations on values in cells and on other worksheets and workbooks. I regularly save the latest version of this Excel file under a new name. But every time I do this it seems to grow enormously. The first version was 734 KB. There are only minor changes in the program since then, but the latest version has grown to 7,7 MB! But there is really no more data stored in it that in the first one. Does anyone have a clue what is happening here, and what I can do?...

Excel 07
I have a stacked chart with one series showing positive values, and two other series showing negative values. When I add data labels, they only appear on the positive value series. Even when I click on the negative series, I get the menu to format the data labels, but in no way, shape, or form are they actually visible on my chart. Any thoughts? Thanks! I used this data Group Alpha Beta A 1 -1 B 2 -4 C 3 -6 D 4 -4 E 5 -3 Made a stacked column chart Added data labels to each data series; both were visible Changed chart type to Line and to Bar and data labels remained visible What to se...

Excel #26
I started a new job, and am having to build a call list for potential clients. When I find these companies on certain websites or what not, they are listed on a page from top to bottom, sometimes in the many hundreds of companies in one column.. Here is a link , to a sample of what I am working with . http://spreadsheets.google.com/ccc?key=0AhkqFcg9vgGhdGhWSnQ2a01mRzlJVU1vVl9fOHZZYUE&hl=en But Imagine Column A cell values go to A4032, Which is how much information I have to enter. : SO I have list with over thousand company?s names and contact info, But, I am getting company in...

Why does it say Book-1 after Microsoft Excel?
OK, I'm having trouble with Excel. I can't get into any of the commands. Why does it say Book 1 after Microsoft Excel at the top of a worksheet. What am I doing wrong? 1. What do you mean by command, do you mean the menu and if so whate happens or what does not happen? 2. When you open excel using default settings excel opens a new workbook called Book1 by default, nothing wrong with that. 3. I have no idea, it's impossible to say with so little information, are you certain you are making anything wrong? Regards, Peo Sjoblom "Sue H" wrote: > OK, I'm h...

exceL NOOB needs help w/sales tracking
I manage a nightclub and want to create a sales tracking chart pe server. This is easy if all servers occupied the same cells on ever worksheet. However, corporate insists that the daily sales report b designed around stations. Therefore, my servers rotate from station t station on a daily basis. I know i could always make a seperat worksheet designed around the servers and input the server totals second time onto that worksheet but i was hoping of finding someway o linking a second worksheet that totals up the sales per server as enter them into the daily sales report (the one designed ...

Import data from Notepad to Excel
Can anyone tell me how to import data on a notepad sheet into an excel spreadsheet? I have been told this can be done but cannot locate the instructions. Thank you. Cindi File>open enter filename and select .txt from the filetype dropdown list. -- John johnf202 at hotmail dot com "Cindi Schneider" <cindi.schneider.amex@wspan.com> wrote in message news:048001c36ce1$03e708e0$a401280a@phx.gbl... > Can anyone tell me how to import data on a notepad sheet > into an excel spreadsheet? I have been told this can be > done but cannot locate the instructions. ...

Turn off font Auto Scale globally in Excel 2003?
Is there a way to tell Excel globally to uncheck the Auto scale checkbox on the font tab for all chart elements? Or at least turn it off for all elements on the current chart? The default seems to be to have auto scale on and the only thing I can figure out to deal with it is to turn off each chart element's auto scale, one at a time. For my situation this is truly an obnoxious default setting. Try first unchecking font auto scale with the "Chart Area" selected (the outside of the chart). You could set your chart preferences as the default chart type, see Chart types, C...

Lock-Down Excel Headers and Footers
Is there a way to protect the headers and footers in Excel 2000 so that they cannot be changed or unlocked without a password? Thanks You can't do this directly, but you can use a macro to restore your headers and footers before printing, say, or before saving. Put this macro in the ThisWorkbook code module (right-click the workbook title bar and select View Code): Private Sub Workbook_BeforePrint(Cancel As Boolean) Dim wkSht As Worksheet For Each wkSht In ActiveWindow.SelectedSheets With wkSht.PageSetup .LeftHeader = "" ...

Speed up Excel
My Excel spreadsheet runs very slow, it has hundreds of formulas. Are there any tips on how to make Excel faster? Alternatively, if I configure Excel to calculate manually (F9) is there someway to know that a calculation is pending? That is, after a change is made to the data in the worksheet can Excel somehow indicate that the calculations are not up to date? Don See Charles Williams' site for speed tips. http://www.decisionmodels.com/ Also, should see "Calculate" on status bar if a calc is required. Gord Dibben MS Excel MVP On Sat, 12 May 2007 16:50:45 -0500, &q...

How do I stop Excel auto formatting the text 3-4 as 04 Apr?
I run a web query that imports soccer results to Excel. Unfortunately the web site formats the result as 3-1 for example. This text is automatically converted to 03 Jan by Excel. I can't seem to stop this happening, even if I explicitly set the cell format to text. Doing so just converts the contents, which were a date, to the internal date number and if I re load the data it reverts to 03 Jan again. Does anyone know how I might get excel to leave the format of the cells alone? thanks ahughf There is no turn off for this, you can either preformat the cells as text or precede the e...

excel closes when trying to open certain files
Excel 2003 will close when trying to open a specific file. When Excel is reopened the file is in the recovery box. When I open the file from the recovery box it gives a message that the file is too corrupt to repair and most of my data is gone along with any formatting. This is the only file that seems to be affected and it use to open without any problems. The same file will open on other computers with Excel 2003 without any problems. The only difference between this file and others that seem to have no problem is before the file opens, it ask to disable or enable macros. This f...

Printing in Excel #4
IN EXCEL 2002 I HAVE TO PRIINT FIRST THREE RAWS IN EACH PAGES AS HEADER, I HAVE MORE THAN 10 PAGES. HOW CAN I PRINT THESE FIRST THREE RAWS IN EACH PAGES? Abdul Salam, if I understand your question, you have an Excel 2002 worksheet that, when printed, extends to more than 10 pages. You want Rows 1-3 of the worksheet to be repeated at the top of each of the 10 pages. Is this so? If it is so, here is how to do it. From the worksheet: 1-File > Page Setup > Sheet. 2-In the Rows to repeat at top: field, type $1:$3 3-Click OK. -- DDM "DDM's Microsoft Office Tips and Tricks"...

Excel / Access User Conference
The 2007 Midwest Excel / Access User Conference will be held on October 24-26 in St. Louis, Missouri. Regular registration will close on September 30th. This event will be featuring Mike Alexander, Damon Longworth, Jon Peltier and Bob Umlas. http://www.exceluserconference.com/MWEUC.html -- Damon Longworth 2007 Excel / Access User Conference London, England - Tentatively Sept. 2007 St. Louis, Missouri - Oct 24-26, 2007 www.ExcelUserConference.com/ ...

Moving from Excel 03 to 07, need help with customizing
In Excel 03, we had a series of toolbars that contained buttons that ran a macro placing a picture on the spreadsheet in the selected cell. There were several toolbars as there were different sets of pictures in different colors. They were used as accounting tickmarks. I can change my macros to run on 2007 (due to 07 not liking the Select command) but I'm having troubles finding a substitute for the custom toolbars. The ones I had in 2003 did come over under the Add-Ins tab, but I cannot update them as needed. Plus there is no way to bring up a toolbar for the differ...

Converting Excel 2000 to Mac OS
Anyone know how to convert an Excel 2000 file to Excel Mac OS so that I can view manipulate the file in Mac? -Stef No need to convert at all.. -- Regards, Peo Sjoblom "STEFAN BEAN" <res1nwz2@verizon.net> wrote in message news:kKhpb.58392$AU.52046@nwrddc01.gnilink.net... > Anyone know how to convert an Excel 2000 file to Excel Mac OS so that I can > view manipulate the file in Mac? > > -Stef > > ...

Set Default Date Short Format in Excel
Hi, Is there a way to set default date format to mm/dd/yyyy, which is my short date format setting for Windows. Right now, when I paste rows of data from Access 2003 into Excel 2003, the date format is dd-mmm-yy, even though format is Access table is short date format of mm/dd/yyyy. Thanks. Alan ...

How do you turn off automatic "save a copy" in Excel
I absolutely abhor the fact that excel saves a copy rather than automatically saves to the original document. Does anyone know how to turn off the "save a copy" feature and to have Excel automatically save to the original document. Cecilia, I think you're referring to Autorecover (Tools - Options - Save tab). To automatically save to the original file, use Autosave. It's in Tools - Addins. There are disadvantages to Autosave. Instead, being in the habit of saving at appropriate times is a good technique to master. Consider versions (do Save as, "Myfile-2....

converting figure into words in excel
Use a look up table. In column A enter 1, 2, 3 etc. In column B enter one, two, three etc. Sort the table into column A ascending order. Highlight whole table and give it a name eg convert. Then use the formula =vlookup(cellref, convert,2) where cellref is the cell with the field you wish to convert. "Manish Jangid" wrote: > Manish Check out the info and links provided by Bob Phillips. http://www.xldynamic.com/source/xld.xlFAQ0004.html Gord Dibben Excel MVP On Tue, 19 Oct 2004 05:51:01 -0700, "Manish Jangid" <Manish Jangid@discussions.microsoft.com>...

Can I use Excel to create a form?
Hi, Is it possible to make a form by Excel? I need creat a form which will need users fill some information such as Name _____ Address _______ The user can only put information on the line but should not be able to change the form. Can Excel do this? Thanks! You can lock cells that shouldn't be changed and unlock cells that can be changed (format|cells|protection tab). After you do that, you have to protect the worksheet (tools|Protection|protect sheet) This kind of protection is easily broken, but it's made for things like this--to stop users from overwriting formulas and l...

Question about number of possible cells in Excel.
The book I'm reading says Excel has 255 worksheets, 256 columns and 65,536 rows. It also says Excel has 16.5 million cells. Is that correct, and if so, how is that calculated? Another thing...it says columns are labeled A through Z, AA through AZ, and so on, up to IV. I typed "IV" into the name box, and couldn't visualize the cell reference. Am I reading it correctly as the letter "I" and the letter "V"? Thanks in advance. Try this in an empty worksheet. Edit|Goto (or F5 or ctrl-g) type: IV65536 That's the last cell on the worksheet in xl97-...

Open and Convert Mac .numbers to excel file format tools
Are there any tools that allow PC user of Excel to open a Mac .numbers extension file? kspalladino@yahoo.com ...

Forgotten Password on Excel Workbook
Hi there Can anyone help me. I have forgotten the password on a worksheet. Is there any way to open the document? thanks mary Mary Which is it? Password on File to open? http://www.lostpassword.com/excel.htm http://cpearson.com/excel/password.htm Password on Worksheet? http://www.mcgimpsey.com/excel/removepwords.html Password on Workbook? http://www.mcgimpsey.com/excel/removepwords.html See links to other sites when at John McGimpsey's site. Gord Dibben Excel MVP On Sat, 24 Apr 2004 09:23:31 -0700, "mary" <anonymous@discussions.microsoft.com> wrote: >Hi...

Excel 14.0
I have a single 23,000 lines of VBA code Excel application (with 28 sheets with lots of data), which migrated from 2007 to 2010 without presenting any unexpected behaviour except for these two solid and re-testable "appearance problems": 1. The frequency of running into a condition like: (Not Responding) has increased in frequency with a factor of 10-20. It is annoying to look at and my product receives some heavy criticism. However, my code is performing every job successfully and perhaps 5-10% faster than under 2007 Excel 12.0, so all is mostly good, but it IS anno...