Hi, Gents
I am not professional in ms excel and So I'm sorry if my question is simple.
I have 4 sheets witch their name is "M1" till "M4", and each worksheet contain a table, which the first column is worker name and others column is work days status
But worker change in these 4 months and so the table is not same!
how can summarise all sheets in the last sheet, in the way all worker, be in that.



the excel file is attached



Book.xlsx

Hi and welcome
you could try using Pivot Tables with multiple ranges

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

In the attached I have used the Consolidate feature.
I have not created a link to the source data, but you can do that if you wish.

Attached Files

Consolidate.xlsx (15.0 KB, 10 views)

In attachment is a version, where:
All attendancy entries are done in same sheet;
(As result), you can have any number of months without redesigning the workbook;
Summary is done on sheet Employees (you can have separate Report sheet, but this don't add any functionality, and adds complexity to design);
You can summarize any number of months, selecting start and end months for report period on sheet Employees.

Attached Files

Consolidate.xlsx (23.2 KB, 15 views)

Thank you, but because my data in each sheet its more than 120 rows and 30 column, the report that generated in this way lagging

this method is so tricky, I looking the way which when operator entry new data to a table, summary and all related table automaticaly updated

Don't understand this sentence
Is the solution good or bad?

Whenever data are edited or a new row of data is added on Attendance sheet, the summarized values for employees on Employees sheet are updated (when month for updated/added row in Attendace is between ReportFrom and ReportTo on Employees sheet. You can select months from month of StartDate on SetUp sheet until month of current month. No need to add new sheets every new month, and then editing all formulas to take data in those new sheets into account. You can use the same workbook probably until Attendance sheet has about 20000 entries (maybe even more, the calculation speed is deciding factor). Having data validation lists for employees and month numbers minimizes the risk of typing errors messing up with formulas.

The list of months on Months sheet is updated automatically. You can prepare the Months table for several years into future (future month number are not displayed but they are in 'waiting'). When you can't select the current month anymore, then this means that the Months table is filled to bottom, and you have to add rows to it - simply drag the last cell in table down.

I advice to set on attendance sheet the autofilter to current month (or previous + current month) - so users don't have to scroll a long way to bottom of table.

1. Someone adds new employees to Employees table (when there were new ones). It may be operator, or someone else when you want to keep operator's part as simple as possible;
2. Operator opens the workbook, activates the Attendance sheet and scrolls to bottom of table;
3. Operator activates last cell in vacation column, and presses Tab. A new row is added to table, with Data Validation lists in place in columns Month and Name. Or enters a number into columns Available, Absent or Vacation with same result. When user starts new record entering Month number or employee name to cell, the data validation is not expanded to this row for this column, When this happens, then the Data validation must be copied to new row (PasteSpecial > Validation) from some uncompromised cell in same column;
4. Operator selects/enters Month number, selects/enters employee name, and fills rest of columns for this employee;
5. The cycle 3-4 is repeated until attendance for all employees for this month is registered. That's all.

Another possible work order is, that before end of month
1. Someone adds new employees to Employees table (when there were new ones);
2. The same person prepares Attendance table, adding new records with month number and employee names working in this month;
3. Operator(s) enter(s) attendance info (3 columns) for every employee.

I think anyone capable to open an Excel workbook is able to work with this one!

I'm sorry, the method is very good, thank you
I' am not professional in excel, so I must check your method, it takes time for me

Thank you