If this = this, then perform this action

[00Able]

I have attached a workbook for better reference...

Here is what I am trying to do...

I have 3 worksheets in the workbook. One for First shift employees, one for Second shift employees and a third for Terminated Employees.

Each day when I do attendence, I use a drop down menu to show if the employees are here, called off, ncns, late, termed etc...each work book is for a week of data. So it shows Monday - Sunday. If in one of those fields, I enter the "termed" option, I would like it to automatically populate the employees name, start date, end date (the date it shows "termed"), and the shift worked in the third workseet "Termed".

So, on the third Sheet it would show Jess Donaldson from first shift was termed on Nov 18 and Harold Bigler from second shift was termed on Nov. 19.

Is this even possible? Does someone have any ideas of how I would go about doing this?

If anyone has any thoughts, suggestions, advice, I would really appreciate it.

[JulieM]

Just a thought, not even halfway to a solution, do you have to have the data for the different shifts on separate worksheets?
I would have thought it easier to have a single sheet for your baseline data, with an extra column to indicate which shift the person was engaged in. You can always use other methods to show/ print out reports for the separate shifts, if that information is required separately.

[00Able]

If needed, I could create another sheet and combine the data and then hide it, or add another column to indicate what shift they are working. I have to keep seperate sheets because the 2nd shift Supervisor, likes to sort by Start Time, and the 1st shift Supervisor likes to sort names alphebetically. So trying to make it compatible for both Supervisors. Also please realize this is a company of 500+ employees, and names are similiar between the shifts. And take in to consideration that the Supervisors using these sheets are not "excel bright", and in the past they sorted data without highlighting all the fields, and needless to say, we had a huge mess to clean up, as some employees ended up with more points than what they actually had. Imagine, terming an employee and having them come back and say "I only have 2 points."

[royUK]

You could use a VBA solution.

Please Login or Register  to view this content.

Copy the code
Select the worksheet in which you the code to run
Right click on the sheet tab and choose View Code, to open the Visual Basic Editor.
Where the cursor is flashing, choose Edit | Paste


Where does the combobox issue in your title occur?

[00Able]

Well the combo box issue is something completely different, and I wasn't sure I was allowed to have multiple issues on the same thread, so I deleted it?

But in each sheet I have approx. 490 input fields (for days of the week worked), all of them have drop down menu's using the data valadation list, which are dynamically referencing as List "Actions", as you can see in cells E75:E92, my issues are:
1. The drop down choices are not including the last option "Transfer"
2. I would like the list to automatically populate, once you start typing "Here" in any cell between F3:L72,(So, As soon as you typed the "H" you would be able to select "Here", as if it was previously typed in that column). So I attempted to add a combo box, to dynamically relate to cells "E75:E92", however when I did that, It would not let me type in that combo box, only let me use the drop down.

So I thought, I could try to create a combo box and then have each cell in F3:L72 automatically reference to that same combobox in hopes that it allows me to perform the functions that I am desiring. Also I did not want to create 490 comboboxes, lol. But again, once I tried it, it wasn't working, and I think I had some bad code, so I deleted it, also keep in mind, I am not that good with VB.

[royUK]

The combobox should really be a separate question.

1. It doesn't include Transfer because the range formula is wrong & that cell is excluded. I'm not sure why you want a Dynamic Named Range, but the formula should be

='1st Shift'!$E$76:OFFSET('1st Shift'!$E$76,COUNTA('1st Shift'!$E$76:$E$65536)-1,0)

2. Once you have entered a term once in the column using the Data Validation it will auto-fill subsequently. I don't think the effort of adding comboboxes is wort hit, although there is a workaround here

[00Able]

Also the VB code you provide supplies only the employee Name and I can not decifer, how it knows to return the value in column E the name, is it this part of the code?
.Cells(NextRw, 1).Value = Me.Cells(Target.Row, 5).Value
and if so then why does it not return the start date as shown with the next line of code
.Cells(NextRw, 2).Value = Me.Cells(Target.Row, 2).Value

...I apologize my vb skills are very basic.

[royUK]

Please use Code Tags in future.

The code fills in Name, Start Date, End Date & Shift

Please Login or Register  to view this content.

I've also modified the range formula

[00Able]

Thank you so much for your help, I am not sure why I was having issues with this initially but it seems to be working seamlessly now.