Terrygordon
asked on
Autofill cells when current cell value changes
Hi All
I have a sheet where activities carried out by various people are mapped across segments of the day. This used to be just am and pm but we have now moved to 4 daily segments for each person (am1, am2, pm1 and pm2).
The codes that identify what each person is doing are quite long so, to make it easier for the admin staff, I would like them to be able to click an option that automatically replicates whatever is typed in a cell, across the other three cells under the persons name. (Most people work on the same project across all four segments - but not everybody, so I will probably build in a disable button for the exceptions).
I am assuming that a worksheet_change event can handle the autofill, but I'm not sure how.
The important thing is that it only autofills to the end of the appropriate set of 4 segments (i.e. that it doesn't overwrite data in other people's columns). For example, in the example below, if an activity code was entered/changed for Person A, am1 (let's assume it's cell A3), it would automatically replicate this content in cells B3, C3 and D3. However, if the activity code is entered for Person A pm1 (cell C3 in this example) it would only autofill cell D3 and not spill over into person B's columns.
A B C D E F G H
1 Person A Person B
2 am1 am2 pm1 pm2 am1 am2 pm1 pm2
3 Code Code etc.
Hope you can help.
Regards
Terry
I have a sheet where activities carried out by various people are mapped across segments of the day. This used to be just am and pm but we have now moved to 4 daily segments for each person (am1, am2, pm1 and pm2).
The codes that identify what each person is doing are quite long so, to make it easier for the admin staff, I would like them to be able to click an option that automatically replicates whatever is typed in a cell, across the other three cells under the persons name. (Most people work on the same project across all four segments - but not everybody, so I will probably build in a disable button for the exceptions).
I am assuming that a worksheet_change event can handle the autofill, but I'm not sure how.
The important thing is that it only autofills to the end of the appropriate set of 4 segments (i.e. that it doesn't overwrite data in other people's columns). For example, in the example below, if an activity code was entered/changed for Person A, am1 (let's assume it's cell A3), it would automatically replicate this content in cells B3, C3 and D3. However, if the activity code is entered for Person A pm1 (cell C3 in this example) it would only autofill cell D3 and not spill over into person B's columns.
A B C D E F G H
1 Person A Person B
2 am1 am2 pm1 pm2 am1 am2 pm1 pm2
3 Code Code etc.
Hope you can help.
Regards
Terry
ASKER
Hi Rob
This doesn't really fit with the purpose of the document. It is basically 8 people with 4 timeslots each(the columns) and 365 rows, each one with a date for every day of the year. I want it so that when you type a code in the first of the 4 cells, it fills the second, third and fourth cells under the name (automatically) or, if typing in the third cell, it fills in the fourth cell automatically.
Regards
Terry
This doesn't really fit with the purpose of the document. It is basically 8 people with 4 timeslots each(the columns) and 365 rows, each one with a date for every day of the year. I want it so that when you type a code in the first of the 4 cells, it fills the second, third and fourth cells under the name (automatically) or, if typing in the third cell, it fills in the fourth cell automatically.
Regards
Terry
Following on from my suggestion you would also have a column for date. Even with this set up you would only have 2920 rows of data. The data can easily be filtered to only show current date or specific person if so required.
However, if you don't want to go that route for layout, you could still use the same formula principle.
First cell of each group of four is blank and then the other three refer to the prior one, so in your example above cell A2 would be blank and B2 would be =A2, copy across to C2 and D2. Then select A2 to D2 and copy and paste into E2, I2, M2, Q2, U2, Y2 and AC2 to cover your 8 people.
The Admin would then fill in each of the same cells mentioned above or if a different code for the afternoon just overwrite in the afternoon slot. You could use some conditional formatting to highlight the required cells if it makes it easier for the Admin.
Thanks
Rob H
However, if you don't want to go that route for layout, you could still use the same formula principle.
First cell of each group of four is blank and then the other three refer to the prior one, so in your example above cell A2 would be blank and B2 would be =A2, copy across to C2 and D2. Then select A2 to D2 and copy and paste into E2, I2, M2, Q2, U2, Y2 and AC2 to cover your 8 people.
The Admin would then fill in each of the same cells mentioned above or if a different code for the afternoon just overwrite in the afternoon slot. You could use some conditional formatting to highlight the required cells if it makes it easier for the Admin.
Thanks
Rob H
Terry,
Take a look at the attached workbook with a sheet for each of the scenarios, your current layout and my suggested altered layout.
I have entered a little bit of data on each, Person1 for 1 and 2 January. On 1 January he/she worked the same code for all four sections of the day but on 2 January he/she worked a different code in the afternoon. The manual entries are in column B for both days and then an extra entry in column D, using Code3. The same is true for my suggested layout but columns C & E have the manual entries. The detail in columns A & B only took a few minutes to create and could easily be replicated for real data.
With the data in my suggested layout I have also enabled the AutoFilter function. You can use the dropdowns in row 1 to hide/show only specific rows eg a specific person, a group of dates. In this format it would also be simpler to do onward reporting of who is using what codes.
Thanks
Rob H
Code-Booking.xlsx
Take a look at the attached workbook with a sheet for each of the scenarios, your current layout and my suggested altered layout.
I have entered a little bit of data on each, Person1 for 1 and 2 January. On 1 January he/she worked the same code for all four sections of the day but on 2 January he/she worked a different code in the afternoon. The manual entries are in column B for both days and then an extra entry in column D, using Code3. The same is true for my suggested layout but columns C & E have the manual entries. The detail in columns A & B only took a few minutes to create and could easily be replicated for real data.
With the data in my suggested layout I have also enabled the AutoFilter function. You can use the dropdowns in row 1 to hide/show only specific rows eg a specific person, a group of dates. In this format it would also be simpler to do onward reporting of who is using what codes.
Thanks
Rob H
Code-Booking.xlsx
ASKER
Hi Rob
The layout in your suggested theme (with filters) just wouldn't work for this application - it does a lot more than just record codes against names and dates and is tied to numerous other sheets and reports involving hundreds of lines of code.
I can see the logic in the first suggestion but the problem is that people change the details and overwrite these cells all the time and often copy and paste text so, it wouldn't be long before all the formulae got wiped out and, once they're gone, the method doesn't work.
I'm playing with the idea of a series of buttons labelled 1, 2 and 3 that uses a VB macro to autofill 1, 2 or 3 cells to the right based on the content of the current cell (using offset), unless you can think of a more robust method?
Regards
Terry
The layout in your suggested theme (with filters) just wouldn't work for this application - it does a lot more than just record codes against names and dates and is tied to numerous other sheets and reports involving hundreds of lines of code.
I can see the logic in the first suggestion but the problem is that people change the details and overwrite these cells all the time and often copy and paste text so, it wouldn't be long before all the formulae got wiped out and, once they're gone, the method doesn't work.
I'm playing with the idea of a series of buttons labelled 1, 2 and 3 that uses a VB macro to autofill 1, 2 or 3 cells to the right based on the content of the current cell (using offset), unless you can think of a more robust method?
Regards
Terry
Hi Terry
I agree with your layout for the "database" part of the work log. Danger with worksheet_change event is that when the sheet is revisited the previous days are overwritten.
However, a couple of suggestions;
Each individual has their own sheet which is then linked to the central consolidation. Then you can use Rob's suggestion of a simple formula to transfer column 1 to column 2-4. [=IF(ISBLANK($B4),"",$B4)]
Use a master code list to validate entries in the cells.
Hope this helps
I agree with your layout for the "database" part of the work log. Danger with worksheet_change event is that when the sheet is revisited the previous days are overwritten.
However, a couple of suggestions;
Each individual has their own sheet which is then linked to the central consolidation. Then you can use Rob's suggestion of a simple formula to transfer column 1 to column 2-4. [=IF(ISBLANK($B4),"",$B4)]
Use a master code list to validate entries in the cells.
Hope this helps
I partly agree with your comment regarding losing formulae but surely there is a lesson to be learnt from doing the input correct first time!! Once a particular day has been completed you shouldn't have to go back and redo.
Anyway, going back to your original suggestion of AutoFill linked to a WorkSheet_Change Event, yes this would be possible and probably fairly simple to code; the code could look at row 2 of the active column and check whether am or pm as to whether to fill right 3 cells or only 1 cell of the active row.
The downside to doing anything with Workbook_Change event is that you lose your Undo history.
Let us know if you want us to continue and write the code.
Thanks
Rob H
Anyway, going back to your original suggestion of AutoFill linked to a WorkSheet_Change Event, yes this would be possible and probably fairly simple to code; the code could look at row 2 of the active column and check whether am or pm as to whether to fill right 3 cells or only 1 cell of the active row.
The downside to doing anything with Workbook_Change event is that you lose your Undo history.
Let us know if you want us to continue and write the code.
Thanks
Rob H
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Some people do their on entries, but most are entered centrally. I agree about shared workbooks (it isn't), and although it can be accessed by anyone with a password to a specific drive, it can only be edited by one person at a time and I have built in a timer that saves and closes it if a user is inactive for more than 10 minutes. :-)
I will have a look at the one button option tomorrow and get back to you. I think I'm almost there with this, but I will still award the points for your input.
Regards
Terry
I will have a look at the one button option tomorrow and get back to you. I think I'm almost there with this, but I will still award the points for your input.
Regards
Terry
ASKER
I have gone down the button route, with a sub that checks the name at the top of the column to ensure it doesn't overwrite someone else's events. I've tested this with the admin team and they like this option best.
As you have both put a lot of effort into this, I am splitting the points if that's ok.
Thanks for your input.
Terry
As you have both put a lot of effort into this, I am splitting the points if that's ok.
Thanks for your input.
Terry
Many thanks I comment out of interest - helps my learning as well.
Person am1 am2 pm1 pm2
Person A Code Code Code Code
Person B Code Code Code Code
Simplest way to then have the code replicated across the columns would be to enter a formula, eg assuming Person A in row 2 and am1 in column B, the formula in C2 would be:
=B2
Copied across to column E would then replicate the prior columns. If the person then works something different in the afternoon, overwriting the formula in column D with a different code would then only affect column E.
Thanks
Rob H