1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel - Using one Workbook to populate information in another

Discussion in 'Business Applications' started by James9988, Aug 31, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. James9988

    James9988 Thread Starter

    Joined:
    Aug 31, 2011
    Messages:
    10
    Good morning,

    My head office has implemented a new way of recording operatives working hours.

    It used to be based on a persons name and then the number of hours worked on a job. You could get about 20 men on one sheet.

    Now thy want it based on the Job so each job has its own sheet, so we are swimming in paper!

    What I need is a page where I can have a drop down box with the following criteria:

    Name
    Trade
    Week Ending

    and then

    The jobs that that person did, so:

    The Job
    The day
    The Number of hours

    I dont want to manually enter all of this in a load of different spreadsheets so i there a way of just using one and when i hit 'Enter Data' or something it populates pre-made spreadsheets that I can print?!

    Ive attached what ive got so far.

    Regards
    James
     

    Attached Files:

  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,580
    First Name:
    Hans
    I think this could be done, but, after I've seen your attached sample I have the following question:
    What does the reulting sheet need to look at?
    What will be the source?
    If I understad correctly you already have a sheet where everyhting has been entered and now wnat / need a button that will produce output of so many sheets as there are jobs with all the employees that worked on the job on it. Correct?
     
  3. James9988

    James9988 Thread Starter

    Joined:
    Aug 31, 2011
    Messages:
    10
    What I need to happen is this:

    On the input page I will select an operative and his trade from the drop down box.

    Then listed from 1-20 (i may only need to use 4 boxes for example) list where this man has been working and what he has been working on.

    So,

    'Sam' the 'Fitter' spent 'Monday' working on 'Variation 1' for '8 hours' at 'Normal Time' .
    The same for 'Tuesday', 'Wednesday' & 'Thursday'.

    On 'Friday', 'Sam' will work on 'Variation 2' for '4 hours' at 'over time'.


    So now ive entered 'Sam' information for that week into the columns.

    Now i would hit 'Enter' or something like button.

    This information would generate One Sheet for Variation 1 and One Sheet for Variation 2. Sams hours would be entered under the specific day in the Normal time column for the right day on Sheet Variation1(N = normal, O= Overtime and P= premium time)


    On sheet Variation 2 Sams name and trade would be entered in the left hand box, and hours would be entered but only on friday in the Over time Row.

    I would then go back to the data entry sheet and preform a similar routine for the next operative whos name and details would then appear under Sams in the resultant sheets.

    PLEASE IGNORE THE END COLUMNS REGARDING TRAVEL, LODGE AND RADIUS ECT (They will be entered manually.
     
  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,580
    First Name:
    Hans
    I'll read it carefully and see if can do something for you.
    No promises, but ... maybe I'm sucessful :)
     
  5. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,580
    First Name:
    Hans
    I think I grasped the idea.
    I entered a name in C2 and a trade in E2.
    Now that the data is en the Entry Sheet, you want separate sheets per variation. Correct?
    Okay, but these sheets are the contenwise a copy of the Weekly Time Sheet?
    Would it be that you will have a Weekly Time Sheet-Variation 1, Weekly Time Sheet Variation 2, etc?
    What happens when you enter another name above, does his Variation 1 go into Sam's Weekly Time Sheet Variation 1 appending data or would this be alltogether a new sheet?
    I don't know how many will be using this sheet and who is going to read the Weekly Time Sheets created, Why not a new file for each variation, no macros, no issues and the risk of losing the data in Do Not Touch! and Variation Code will be limited, I would even suggest hiding the so that people won't edit the by accident.

    So let me know what the final idea is, not in words, fille a sheet with what you want it to look like, that is easier to understand "A picture tells more than a thousand words"
     
  6. James9988

    James9988 Thread Starter

    Joined:
    Aug 31, 2011
    Messages:
    10
    Ive attached a worked example. At the end of it is a diagramatic overview of what the process should entail.

    Variations = Work Location

    Job Type = Type of work happening.

    So there are up to 50 Work sites and 7 types of Job this could produce up to 350 time sheets!

    This would never happen so I only need a sheet when a job happens at a variation. (Approx 14 sheets, which isnt a lot but compared to the single sheet we used to fill out it is!)

    Hope this helps and I truely apprciate your assistance!
     

    Attached Files:

  7. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,580
    First Name:
    Hans
    Hi James,
    This last sheet confirmed my thoughts and yes it helps.
    I do think I can put it together for you but I will need a little time to build it.
    One thing jst to meka sure, I noticed that you used the Variation 9- Jog type as sheet name, the max allowed characters is 31, so I do hope that all Job names are unique inside the 31 character spawn or else they will consideren as duplicates.
    Of course, the Variation x- makes a difference but ...
    Just a little pataience then, I'll be taking a swing at it during the weekend.
     
  8. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,580
    First Name:
    Hans
    Hi James,
    I put together the following and think it's what you need / want.

    Ít's almost bedtime here so just a short explanation. I'm putting some explanation in a word document explaning some things and some additional ideas I have that maybe you should think about.

    Right now, the code will create a different timesheet for each combination or variation and job type

    I made some changes to your Do Not Touch! sheet like eliminating the - at the bottom and making the named ranges dynamic to avoid long lists with a lot of - at the bottom.

    If you add a new employee or Job Type the you have to run a function called SetNamedRanges, this function is also called when the sheet opens so the data is always up-to-date.

    The week ending column I also made dynamic, the formula in the 2nd row calculates the Sunday 2 weeks back and based upon that the list is updated automatically, so 'Do Not Touch! :)

    You Press the GO button and if the data can be used you will be prompted to continue to process.
    If data is incomplete to process then you get a message too.

    I generally put a version number and build in the Application title bar now its V0.1(110903), this helps to know we're talking about the same version when editting. The filename has the same version in the name but that can be changed.

    I added a Blank Time Sheet (Hidden) to use as template for the new sheets, don't change this sheet's name or it will not function
    Give it a swing and I'll see your findings. Explanation follows, but first to bed:p
     

    Attached Files:

  9. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,580
    First Name:
    Hans
    Small correction. not directly visible but very important.
    Use the version attached here
     

    Attached Files:

  10. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,580
    First Name:
    Hans
    Hi James,

    I have attached the last version and a word document with a short explanation of what I did and some suggestions that I have come up with while working with your sheet.

    Let me know it if works as I hope it will and I would like some feedback if you have any.
     

    Attached Files:

  11. James9988

    James9988 Thread Starter

    Joined:
    Aug 31, 2011
    Messages:
    10
    That is absolutely amazing!

    Thank you very very much it works perfectly.

    Could you add the following Numbers into the Hours though (I dont want to mess around and break it!)

    1.1
    2.4
    4.4
    7.6
    10.5
    11
    11.5
    12

    Regards
    James
     
  12. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,580
    First Name:
    Hans
    Good morning James.
    It's funny you ask this, I was wondering why the chosen values.
    My advice, and you can chnage it yourself is to chage the datavalidation for the Hour column

    If you select the first cell G5 and change the Datavalidation.
    Allow : Decimal instead of List
    Data: Between
    Minimum: 0
    Maximum: the max numr of hours per day (12?)

    If you check the box Apply these changes to all other cells with the same settings, then the whole column will change.

    You will no longer have a pick-list but you can enter any value with a decimal als long as it's >=0 and <=12
    The "~" will no longer be accepted as valid but could be the ddefault on an empty Entry Sheet

    BTW, did you read my word document and tare you usign the last version Vs.0.2(110904) ?
     
  13. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,580
    First Name:
    Hans
    If you want to keep the Pick List you can also edit the vba code in the
    Search for the line reading:
    ws.Range("I1:I21").Name = "Hours"

    And replace it with
    ws.Range("I2:I" & ws.Range("I" & 50).End(xlUp).Row).Name = "Hours"

    Now you can add numbers up to row 50, I do advice to have the numbers sorted, so if you insert a number then right click on the cell and choos for insert and Shift cells down.

    Let me know if you need help, but I'm sure you can work this out.

    Consider the Data validation as numbers, but, then people van enter any value such as 1.1 1.2 1.5 etc

    Also don't forget to format the column to either one or 2 places after the decima. The time sheets are 0.00,

    Another option is to add a control to round it to one decimal in case somebody enters 1.2345 with onde decimal will become 1.2
     
  14. James9988

    James9988 Thread Starter

    Joined:
    Aug 31, 2011
    Messages:
    10
    Excellent Hans,

    I can't begin to thank you enough. You're worth your weight in GOLD!

    I've shown the management and they love it! All credit was given to yourself though!


    Thank you very very much!

    Regards

    James
     
  15. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,580
    First Name:
    Hans
    You had the sheets, I just translated what you needed.

    Which option did you choose? The Data validation or the Pick List?

    Have you considered creating the Excel sheets in a new document?
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1015267

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice