Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 235
  • Last Modified:

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
0
Terrygordon
Asked:
Terrygordon
  • 5
  • 4
  • 3
2 Solutions
 
Rob HensonIT & Database AssistantCommented:
For starters I would put this into more of a Data table format with the Person as a column rather than additional columns to the right ie:

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
0
 
TerrygordonAuthor Commented:
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
0
 
Rob HensonIT & Database AssistantCommented:
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
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Rob HensonIT & Database AssistantCommented:
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
0
 
TerrygordonAuthor Commented:
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
0
 
cbenCommented:
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)]. Don't protect the formula so if different code is needed they can over-write. This has the advantage of having each person responsible for their own list and making new people and changes much easier to incorporate.
Use a master code list to validate entries in the cells.
 Hope this helps
0
 
Rob HensonIT & Database AssistantCommented:
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
0
 
cbenCommented:
Hi
Just an example of the individual sheet taking on-board your worry about losing formulae.
Macro for the individual to add new row each day.
If you have 8 people assessing one file 4 times per day there will be many times when conflict occurs so individual files are better.
Also change log sheet (would normally be hidden) so you can see if things are going wrong in the use of the file.
Work-Log-Individual.xls
0
 
Rob HensonIT & Database AssistantCommented:
Further to earlier comment, rather than having multiple buttons for 1, 2 or 3 cells to fill, you could probably just do it with one button and make the routine check the value of the am/pm row for the current column; like I was suggesting earlier but on a button click rather than a Change Event.

Can you confirm whether cben is correct with comment regarding each individual doing their own entries or are they done centrally by another person?

If it is one central file that multiple users have access to can I suggest you steer away from invoking the Shared Workbook feature. This is notoriously unreliable and can cause multiple types of corruption; file bloat, calculation lagging, loss of data etc etc.

Thanks
Rob H
0
 
TerrygordonAuthor Commented:
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
0
 
TerrygordonAuthor Commented:
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
0
 
cbenCommented:
Many thanks I comment out of interest - helps my learning as well.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 5
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now