Solved

Autofill cells when current cell value changes

Posted on 2014-09-15
12
210 Views
Last Modified: 2014-09-18
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
Comment
Question by:Terrygordon
  • 5
  • 4
  • 3
12 Comments
 
LVL 31

Expert Comment

by:Rob Henson
ID: 40322818
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
 

Author Comment

by:Terrygordon
ID: 40323032
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
 
LVL 31

Expert Comment

by:Rob Henson
ID: 40323057
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
 
LVL 31

Expert Comment

by:Rob Henson
ID: 40323108
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
 

Author Comment

by:Terrygordon
ID: 40324995
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
 
LVL 1

Expert Comment

by:cben
ID: 40325012
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 31

Expert Comment

by:Rob Henson
ID: 40325016
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
 
LVL 1

Assisted Solution

by:cben
cben earned 250 total points
ID: 40325128
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
 
LVL 31

Accepted Solution

by:
Rob Henson earned 250 total points
ID: 40325707
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
 

Author Comment

by:Terrygordon
ID: 40326452
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
 

Author Comment

by:Terrygordon
ID: 40329808
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
 
LVL 1

Expert Comment

by:cben
ID: 40330704
Many thanks I comment out of interest - helps my learning as well.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now