How to create this report in Excel?

Question

In excel there will be a spreadsheet that will have collums such as account #, name, salary period one, salary period two....ect. On the other pages in the workbook I need to create different sheets for each account # summarizing the salary's paid for each individual. These reports need to indicate the account number, name and summarize the salary's. When I ask how would you create this report please explain the types of functions you would use. I may have to familiarize myself with them. How would you create this report? Your help is greatly appreciated.

Answer 1

Since you are creating a sheet for each account number, I suggest you put the account number and name in the top row or two of data, or on the Header and/or Footer. That way it will show, but not on every row of data.

If you have a salary Period 1 in B2 and a salary Period 2 in C2, you can add them together in cell D2 with a formula: +B2+C2.

If you have a list of several payperiods in column B and C and the total in Column D and you want, say a year to date of total salary, you can set up your period numbers in Col A, number 1 to 26 and the row after that in column D, you can use the =SUM() function to add up the whole column. If the numbers are in D3 to D28, the formula would be =SUM(D3:D28)

If that formula is entered in cell D30 and you have several account tabs inserted all with the YTD total in the same cell (D30), you can create a total tab and enter a formula to add all the totals up.

That formula uses a reference to the sheet names of the first and last sheet. My example assumes the first sheet is Sheet1 and the last sheet is Sheet4. You would have to substitute in the sheet names from your worksheet. Here is the formula:

=SUM(Sheet1:Sheet4!D30)

To enter formulas, put your cursor on the cell where you want the formula and enter either the equal sign (=) or the Plus sign (+) and then the rest of the formula.

To insert a new worksheet in your workbook, click Insert, Worksheet. You can move it by dragging the tab where its name is and rename it by right clicking on its name.

To put something on the Header or Footer, do View, Header and Footer.

Help can give you a lot of short ways to do things, open up help and type in things you need like: Enter formula, Header, Insert Worksheet and 3-D reference.

3-D reference is the one that has the sheet tab name in it.

Good Luck!

Answer 2

You need to manipulate the data to find the information you want. You can't do this in Word. It's easiest in MS Access if you know how, but in Excel you can do what you want as a manual exercise. After you've extracted the information you want from the data you have, it's just a case of setting up an Excel sheet as the source for a mailmerge in Word and produce the certificates.

Answer 3

I guess pivot table is the best approach when u want to show ur data as report (with summary / calculations). In pivot table u can add custom formules. And u just have to refesh pivot when data changes or updates.
This avoids the use of custion formules or functions which u may have to construct, in case of not using pivot table.

Answer 4

I am using these stuff for now 6 years, I need more details, send me your address and we can create it using
VLOOKUP, MATCH, INDEX, OFFSET, and many more powerful functions.
I tried to answer your Q but it seams too long to type in here.

Read my profile, I am XLMan

Answer 5

Try microsoft templates, that might have what you need..http://office.microsoft.com/en-us/templates/default.aspx
.steve