+ Reply to Thread
Results 1 to 2 of 2

Fill blanks from other tabs

  1. #1
    Registered User
    Join Date
    Lancashire, England
    MS-Off Ver
    Excel 2010

    Fill blanks from other tabs

    I have created a holiday chart which I would like to use for holidays, sickness etc. I have filled in January and the first employee on the summary tab but is there an easier way just to auto fill for each month and each employee (approx 50) rather than doing this in each individual box?

    Also when I will use this for every year can the date auto fill for each month automatically for that month in that year?

    The spreadsheet is attached.

    Thanks for your help

    Attached Files Attached Files

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac

    Re: Fill blanks from other tabs

    You could group all sheets by selecting them. Then any change in one sheet will be copied to the others.

    I hate to see people making the same sort of mistake I made in my very early days.
    You are making the same mistake that I see time and time again. You are mixing up the two elements of data capture and final reporting. The two require quite different treatments.

    A lot of people start by designing the form that they expect to see as the final report or which at first glance seems the best way of capturing data, and then wonder why it's so difficult to subsequently analyse and summarise or extract information from it. Yours exhibits all those features.

    You should always capture data in a simple two dimensional table and worry about reporting information from it afterwards. Without exception doing this you will always be able to easily obtain management information. Rarely is this the case if you start the other way round.
    You will also throw open the whole wonderful world of the powerful Pivot table functionality. As a general principle if you find yourself creating separate sheets for stuff like months, departments, expense types, names...etc. then stop and think. Almost certainly a single sheet database would be better.

    In the attached I've created a Data Entry sheet, a sheet to record peoples names, locations and absence types, and a Pivot Table sheet and some slicers for reporting.

    The data entry row 3 is just an example. After selecting a date and selecting a name, location and absence type you would press a button and the new data would be added to the database with a macro.

    This could be improved on since no doubt when holidays are entered they would be consecutive. To cater for that, F3 could become a cell for entering a number of consecutive days and the macro would then automatically add the relevant number of records to the database.

    The pivot table is just one current view. Beng a PT you can dice and slice it as you wish to show alternative views of the data.

    If you wish to pursue this approach further and want the macro adding to update new records then let me know
    Attached Files Attached Files
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 4
    Last Post: 10-25-2016, 01:59 PM
  2. fill in the blanks
    By pmugagga in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 06-07-2013, 10:54 AM
  3. Average tabs ignoring blanks
    By jbwizoz in forum Excel General
    Replies: 3
    Last Post: 11-16-2011, 05:00 PM
  4. Fill Blanks
    By VBA Noob in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 11-20-2006, 05:06 AM
  5. Fill in the blanks!
    By Bhupinder Rayat in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-02-2006, 12:50 PM
  6. RE: Fill in the blanks
    By Jim Thomlinson in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-07-2005, 03:45 PM
  7. [SOLVED] Fill in the blanks
    By BOB in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-31-2005, 03:06 PM


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1