Thread: Excel Help

Read up on Defining Named Ranges. I suspect this will answer what you are trying to do. This defines a Cell or Range of Cells to a given Name. You then use this name instead of the actual cell. eg Smith in place of Sheet1!$M5

I think you'll find that as you haven't fixed M5 by using the $ prefix, e.g., $M$5 or variations thereof, Excel will automatically take care of additions to the sheet.

Have you tried it?

I'll start by saying you should be using a database such as Access for something like this for the exact problem you are having - the data can become random by changing the order the employees are listed on the spreadsheet. A database doesn't worry about that sort of stuff, it just creates a new query (spreadsheet) according to your criteria.

It sounds as if your annual totals/ averages are going to include figures based on each employee (hence your concern about the order of the employee list). If so, you may want to base a formula on vlookup or hlookup which look up a value in a table (in this case name) and return a value from another column corresponding to the first value. In other words, lookup a value.

Have you considered using just one worksheet for the entire year? You'll have 52 columns of weekly figures, bur by freezing the screen on the employee name column you can scroll the values so the current week is always on screen.

Excel is very good at manipulating data, but lousy at storing it, so you should think very carefully about what you are trying to achieve before creating and populating the workbook.

HTH

As dmdigital says, use named ranges. A better way would be to write a macro to summate the data onto a summary sheet, in lieu of formulae referencing other tabs.

Hi

Suggest that you have a look at the use of Excel pivot tables to summarise the data.

These are the best thing going for the summaristion of data in Excel.

For pivot tables to work you would need to store the data in a single sheet with the data in a continuos series under headings such as:
Date,Employee Id, Employee name,Output.
A vlookup could be used to populate the Employee name field once the employee Id has been entered.

The pivot table based on data in this format can then easily summarise the data daily, weekly, monthly, quarterly or yearly.

Depending on the version of Excel you are using there is various levels of explanation on the use of Pivot tables in the Excel help files.