Validating repeated values, then bring the result to another cell

[souza.eq]

Guys, at the beginning I thought it was simple to do, but I couldn't make it happening.


I have a sheet that I organize where people will work at, I have many possibilities and I can use the same employee many times, unless he's already working at some other place.

I need to warn the user (paint in red, msgbox, or anything) that he can't use the employee again (at that date) becouse he is already working in other location at that exactly day. He can use hem another day. If I wasn't clear just look at the uploaded sheet.

Another thing is I have to bring the status for the employee sumarizing his activities for all month at cell E131. Just bring the status from all place he has worked.

I hope you can help me, could it be done just with lookup and data validation or do I have to use a conditional code in vba?

[eins]

Hi,

please find attached workbook. I hope this is what you need.

I cordially greet

[souza.eq]

Hi,

please find attached workbook. I hope this is what you need.

I cordially greet

Great Eins, So much exactly what I needed. I've never would write a code like this, because and I don't have such expertise yet, but I'm trying. Can you plz explain to me the code?

1° - I saw you defined the range as kom

2° - How it works the condition you did comparing one range with another? (For Each kom In Target)

3° - I think this part is the Heart of the code, can you explain to me exacty the steps it does? Please!!!

If Cells(kom.Row, 5).Value <> "" And cl = Cells(kom.Row, 5).Value And cl.Row <> kom.Row Then
If Cells(cl.Row, kom.Column) <> "" Then
Cells(cl.Row, kom.Column).Interior.Color = RGB(255, 0, 0)
testValue = 1
End If
End If

One more thing I have to do is to if everything is OK (no repeated values/red cells) the sheet copy/search the values from the "employee" for all days he worked (days 1-31) wherever he worked on (all the "rigs" he have worked that month) and paste/bring to the summarized field bellow where the names were defined.

e.g.
employee 1 worked 5 days on Rig 1 (E - values)F8:N8, 5 days on Rig 2(K10:N10) and 5 days on rig 4 (O18:S18).

Bring those values to employee 1 F131:AJ131

Another thing can I reset the color to none ( no red/blank) if the condition is no more valid? I'm asking this because if I activate the condition the cells become red, than I remove to reschedule the person, the cell remains red, that way I have to clean the format manually myself.

Thank you so much!!

[eins]

Hi Souza,

1°, 2° Code verifies, whether a cell value has been changed (for each cell from Target range).

3° Mentioned code verifies if there's an employee on the list (in col E : Cells(kom.Row, 5).Value <> "") and whether the employee has to work in more than one place (cl = Cells(kom.Row, 5).Value And cl.Row <> kom.Row). The condition : Cells(cl.Row, kom.Column) <> "" verifies, whether there is an information on employment 'E'.

I think I am not really good at explaining the code, but if you need advise about that one, please do not hesitate to send me private message.


In attached workbook when you change the information on employment (F:AJ), the summary is updating automatically (row 131 and next).

There's a kind of conditional formatting in attached workbook.

Mit freundlichen Grüßen
Eins

[souza.eq]

Hi Souza,

1°, 2° Code verifies, whether a cell value has been changed (for each cell from Target range).

3° Mentioned code verifies if there's an employee on the list (in col E : Cells(kom.Row, 5).Value <> "") and whether the employee has to work in more than one place (cl = Cells(kom.Row, 5).Value And cl.Row <> kom.Row). The condition : Cells(cl.Row, kom.Column) <> "" verifies, whether there is an information on employment 'E'.

I think I am not really good at explaining the code, but if you need advise about that one, please do not hesitate to send me private message.


In attached workbook when you change the information on employment (F:AJ), the summary is updating automatically (row 131 and next).

There's a kind of conditional formatting in attached workbook.

Mit freundlichen Grüßen
Eins


Very nice... I'm learning so much with the code, a lot of study ahead yet. It worked like a charm the removal of the format conditional. What lasts is the step to bring the value to F131:AJ131, the book is bringing the "Function" title on C6 and not the "E"s and "V"s. on F6:AJ6 for the employee 1. Can you help?

[eins]

Hi again,

It's nice to read, that it will be useful to you ;)

Please find attached Workbook.

If you want to improve something and you need my help, just let me know.

Best wishes,
Eins

[souza.eq]

Hi again,

It's nice to read, that it will be useful to you ;)

Please find attached Workbook.

If you want to improve something and you need my help, just let me know.

Best wishes,
Eins

Perfect, now I'll try to adapt to my work sheets with all the data, if I have any problem with that I'll come back to ask for some help. You were of great help on my daily basis work. I hope one day I get to know vba like this! Have a nice weekend Eins!

[souza.eq]

I'm studying the code to learn this new structure for me! It's a pretty useful validation for other situations.

[souza.eq]

Hi again,

It's nice to read, that it will be useful to you ;)

Please find attached Workbook.

If you want to improve something and you need my help, just let me know.

Best wishes,
Eins

Eins, starting the week I'm here trying to make it work with my data, I changed the intervals and its running perfectly. The only issue is that when the sub summ is bringing the values form the table, its overwriting the other cells (possible because of the command Range("F198:AJ" & LastRowColE).ClearContents) , in other words, the employee worked days 1, 2, 3 and 4, then he worked only days 25, 26 and 27. Between this days I manually changed at line 131 (summarizing table) the days off with another status that can change due to the employee conditions, so a sort of letter that can change. When I change the schedule the sub summ overwrites not only the days he worked but all days in the interval. There would be a way to bring only the not empty cells to the summarize table at line 131?
At summ I didnt understand the range

Sub summ()

LastRowColE = Range("E65536").End(xlUp).Row
Wich range is this?

It happens that I just put my silly head to think a little and just put the clearcontents line in comment and it worked like a charm.

Thank you!
Tank you, have a nice week!

[eins]

Please find attached Wbk. I hope my understanding of your post is correct. If something is not in line with your needs, let me know please.
You're right, Range("F198:AJ" & LastRowColE).ClearContents has been used for overwrite values (to be precise, it clears the result table).

The range : LastRowColE = Range("E65536").End(xlUp).Row is for determine, which row is last (End(xlUp) gets first not empty row, starting from row 65536 in col E). Maybe it is not necessary here, as the number of employees is fixed and the range of table is fixed. It was here for 2 reasons: 1. if you change the number of employees, it will still work, 2. for educational ;)

Have a nice week and good luck in the implementation of the new solution!