Microsoft Excel Question

jotate

Despite having a degree in Computer Science, I know as little about Excel as an average person. It's just not what I've worked with. Many of my tasks at work now require that I work with it. Generally, I can find the answers for things on Google, but I'm having trouble putting my current situation into a phrase that works for a Google search. So if any of you know the answer or can find the answer, I'd appreciate it.

The problem:

Basically, what I'm trying to do is print a calendar for every employee. All the calendars will be the same except for the employee's name, the office where they work, and their date of hire at the top of the page. I need some way to take this plain calendar with blank spaces for those three cells at the top and fill them with the appropriate data that's contained in a list on another sheet in the workbook.

Sheet3 has three columns listing every employee name, office location, and date of hire. I need a way to autofill the cells on Sheet1 with the calendar for each row of the Sheet3 list and then print them.

The obvious alternative is copying them one by one and printing them individually. I'm really hoping there's a better way to do this because there are several hundred employees on that list.

Thanks in advance.

FunkyWaltDogg

I am nearly certain that such a thing exists, because my wife does something very similar to generate form letters. Unfortunately I have no idea how it's done.

embrik

It's called a Mail Merge.
I've not tried it from one sheet to another, but it should work...

pslong9

Are you printing the data from Sheet1?

What I would suggest would be to record a macro where you copy and paste the data for one person, print off the calendar, then stop recording the macro. Then you can see the macro code (which is definitely needed for the print code, I don't think what I put below is correct). You can then modify it to make a loop that would cycle through everyone's data. A real quick and dirty macro that might give you a framework is below. If you do this, I would definitely comment out the print command at first and step through it so you make sure the data is coming in correctly before you print off tons of calendars with mistakes.

---

For i = 2 to xxx (assuming row 1 is header, data starts on row 2, xxx is the value of the last row)

Sheets("Sheet3").Select
empname = Cells(i,[column where name is]).Value
location = Cells(i,[column where location is]).Value
startdate = Cells(i,[column where date is]).Value

Sheets("Sheet1").Select
Cells([row],[column]).Value = empname
Cells([row],[column]).Value = location
Cells([row],[column]).Value = startdate

ActiveSheet.Print

Next i

---

Orogogus

I want to say that Excel doesn't do mail merge except to provide the recipient list, but it's hard for me to check because Office Help became a total piece of crap after it went online.

So what I would do is create the calendar in Word and then go to Word, Tools -> Letters and Mailings -> Mail Merge, and then it should be fairly self-explanatory.

This is in Office 2003, so it may have been changed for better or for worse in later versions.

VThornheart

I think you'd have many more options available if you were doing it in, say, Microsoft Access.

Then you could make a report that has a calendar as the "details", and have it automatically create one for every employee in a theoretical "employees" table. If you haven't played around with the "reports" functionality in Access (or with Access at all yet), I can help you out with some further info and links if needed.

I've found that almost anything that one gets the urge to begin doing in excel, one should probably do in Access instead. I've found this out the hard way after many, many times of starting something in Excel and inevitably reaching its limitations and having to switch to using an Access database to get the (more) advanced functionality needed.

Access hits this wonderful sweet spot between a simple file and pro grammatically creating your own solution to the problem. I think you'll find it quite desirable whenever you'd usually use Excel.

EDIT: If you're interested... to start, make an "Employees" table with the same columns in the same order as the excel spreadsheets' "sheet3" you described. Copy all of the excel rows, and paste them into the table you just made. They should instantly import into Access, and you'll be able to immediately start making more powerful Reports (such as a report with a calendar for each employee with the Employees' name/etc... as you wanted to). No need for macros, mail merge, or other possibly cumbersome workarounds to make it function.