group cells in row.

[nucu]

Helo:
A while ago (two months ago) I was asking for help in order to solve problems with a macro, I received a rapid and usefull answer, but then I had some problems with my PC, (changing the HDD), which stopped my plans, than I was very busy and I didnīt find the time to answer, but I do want to thank to those who were helping me in that issue.
As I restart doing the file, I have a new problem, and I am asking once again for help.
I have a cleaning business, and I want to solve the schedule problem with excell, because a special soft is costs minimum 3000 euro, and for the moment I have no echonomical posibilities.
The idea of this is make a schedule of my 50 employees, which are working in around 40 work places, every one has a different number of hours an shift planning.
So I make the central sheet, which has a list with the workers and I copied with linked cells in all the rest of the sheets (the daily program). In this moment I have only one day (1.03.2013, which has the planification. In the C column there are the names of the workers, then the work place from 6 to 22 hours. There is a list of the workplaces ( in the "table" sheet)
As the time pass, appears new wokers and new working places, so I had to imagine how to maintain updated the workers list, and have always a current number (this helps me when I want to know how many workers were in a specific moment). So I define an end date and I compare with the TODAY() if the worker is active o no. Than I count the active workers and this is how I find the number of workers that I have in a specific date. It may be a little tortuous, but this is how I thought.
The problem appears when I sort in alpfabethical order the workers, in the central sheet. Because the cells are linked, what shows is the new value after the sorting task. So the results is that is changhing all the schedule because of the new workers introduced.
I attached the file (I deleted the real name of the workers). In the central sheet appears a new worker "Angel". If I sort in alpfabethical order, the action expand to the other cells in the central sheet, but it does not do the same in the day sheet. So when I sort the workes, "angel" becames the new number one, and has the schedule of the antigue number one, "Jane". I understand that this is the idea of the linked cells, but I donīt know how to somehow group the cell that when I assign to Jane a schedule, it does not change.

Well, I hope that someone understand something. I spend a lot of time thinkining if thereīs another, solution, I know it is.
Thank you.

[aydeegee]

I wonder whether vlookup will help? I have made a few alterations to your sheets - let me know if you do not understand any.

I have swapped columns B and C in CENTRALIZARE. This is not absolutely necessary, but it enables vlookup to be used, rather than INDEX and MATCH

Hope this helps

Regards
Alastair

[nucu]

Helo:
First of all, thank you for your answer
I think it may be usefull, but now the problem is that in the daysheet, in this case the 01.03.2013 I have to manually insert the ID number of the employee, which is somethig that I want to avoid, because it means that I have to manually insert this in every sheet
If you had another idea of how to organise that, please tell me.
Thank you, again.
Nucu

[aydeegee]

Hi Nucu

To set up the next month's sheet, create a copy of the current month and re-name. This will give you all the employees - you can delete those who left during the current month.

If you do have a lot of changes in staff, you can filter the CENTRALIZARE, excluding those who have left, copy and paste the ID numbers into the new month.

Let me know if you want further explanation.

Regards
Alastair

[nucu]

Helo,
The problem is that I have to do this daily, because every day it may be something new (there are 50 employees, 40 workplaces, and I have to think to new emploees or new workplaces)
So make this changes every day is not an option.
I somehow want to group the cells in the row, in order to mantain the same planning when I do sorting in alphabethical or ID number, (which is the number that can give me an idea of the time that had spent an employee in the company)
Thank you.
Nucu.

[aydeegee]

Nucu

I am having difficulty in getting my head round this. (It is probably due to my age ) What do you have to do daily? I thought that the sheet 01.03.2013 was a monthly sheet? Can you let me have a sample of 2 days, so I can see what is required.

Regards
Alastair

[nucu]

Helo,
The sheet 1.03.2013 is a daily planning, from 6.00 a.m to 22.00 pm.
the nexy sheet would be 2.03.2013, the next one 3.03.2013, and so on.
Every day has diffrent planning, due to the needs of the business partner. for example, a bank needs cleaning two hours from 6.00 to 8.00 every two days, another one only one hour in the evening, every workday and another one wants a general cleaning saturday, with 15 workers.
please read once again my first post, I wrote there more detailed, right now I am writing from my phone and its a little bit difficult.
Thank you,
Nucu.

[aydeegee]

Hi Nucu

I have started again. The jobs tab can contain all job relevant data. The operatives could also include rate of pay.
The monthly sheet should have the daily column copied into the next day's column after the next day's sheet has been prepared.
To prepare the next day sheet, right click on the tab / move or copy / click on the latest day (to get the sheet in the correct place) / click check box Create a Copy.
Rename the sheet. Note: the sheet name MUST be d.mm.yy (or dd.mm.yy for 10th onward) otherwise the monthly sheet will not work.

You can sort the daily sheet in whatever order you want, the data will not be changed. The sheet will be a copy of yesterday's sheet, so there should noy be many adjustments to make. Instead of "activ"/" inactiv" I have done a conditional format to show orange for anyone who is not available.

The monthly sheet totals can then be put on an annual sheet (not yet done)

Obviously it is only a test document. I'm very happy to assist further, if you think it will work for you

Regards
Alastair

[nucu]

Helo:
As I see, I wrote an answer yesterday but I donīt know for what reason ít didnīt arrived..
I was telling you that this kind of of plannindo doesnīt fit for me, because I want to see in the first column the employees, with their ID number and the current number (this is important to me, as I print many reports and I need to know how many employees have in one specific moment. Thatīs why I used the active/inactive function, because I count the actives employees.
At the same time, as the sheet shows the planning every hour, I can have a general idea of the activity. I can easily find where is an operative and how many employees have in one workplace at once.
I think I found a solution, using the vlookup function that you mentioned in your first post. Only that Iīll fill the ID number untill 300 in the daily sheet (write now, the last ID_employee is 149). That way, when a new worker arrives, I only have to asign him an ID in the central sheet (CENTRALIZARE) and write his name. I think this is how I avoid the problem.
Which I donīt know is how to hide the rows where the vlookup function doesnīt find the name for the ID_employee (for the future workers). Do you know how to conditionaly hide this rows?
thank you,
Nucu,

[aydeegee]

Hi Nucu

Instead of using any conditional status, you could just use the filter. You can select not to show the "N/A" items. Let me see your new Lookup" schedule if you need further assistance.

Regards
Alastair

[nucu]

Helo Alastair.
I realized now that I didnīt answer to this las post, althoug I red it. I finaly used the filter selecting not to show the inactives employees, which is the same.
I have now only the probem with the hiperlinks, which is a new thread in the macros area, still no answer untill now

Thank you for your help and solu