Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Summarising Data

    Loungers,

    Please see attached sample spreadsheet. I'm trying to work out away to populate cells in the WHS tab from the Doc Numbers tab based on the Type name (see D3 and C4). This would also have to add rows as the number of matches increased. Would need to collect both Plans and Manuals based on their type (ie Safety or environmental)

    I'm thinking some sort of VBA would be required, but I'm not too good at that - any thoughts or examples would be approcaited.

    I hope all that make sence

    Cheers
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    could you post an example of what it should look like after the code is run?

    Steve

  3. #3
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    better example

    Steve, sorry I must have attached the wrong example previouly. I hope the attached explains what I'm trying to achieve better
    Attached Files Attached Files

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Some questions/comments:
    From Doc Numbers You want the items of Type Safety in the appropriate category (from row 5 gray columns, Plans & Manuals combined, others separate) in the WHS sheet
    From Doc Numbers You want the items of Type Enviromental in the appropriate category (from row 5 gray columns, Plans & Manuals combined, others separate) in the Enviro Sheet
    All other "types" (Plant, Quality, Procurement, Training, etc) are to be ignored?

    On the Enviro and WHS sheets, the various category sections (Policies, Procedures, etc) have a certain number of rows. You mention expanding the number if there are more than the number of rows. Should the number of rows be decreased or is there a minimum? For example, the code could have just the right number of rows for the list, then have a completely blank row, then the header for the next section.
    Will the Enviro and WHS sheets, have any Docs listed when the code starts and if so should they be deleted or others ones appended? Will the "blank" sheets be there or do you want the code to just create them, or do you want the code to check first? [The code could start with just the Doc Numbers sheet and create the 2 (or more) summary sheets.

    Steve

  5. #5
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Steve, Thanks for the reply.

    Answer to the first 2 questions, yes. utlimatly there will need to be other sheets to accomodate Plant, Quality, Procurement, Training and any other types I identify.

    The row in the Enviro and WHS sheet can expand or contract to accommodate the number of related documents, I think the blank row and the ne header for the next section would be the way to go. The blank (new) sheets can be there so will just need to populate them with the related documents in their respective sections, although having the ability to create new summary sheets could be worth considering.

    I hope this helps, thanks for your assistance

    Regards

    Dean

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I worked on the logic a little last night when I had the chance and have a plan, I just need to work through, and I just needed some formatting details

    The original spreadsheet had the WHS sheet with more columns and some header info, the 2nd example had only 2 formatted columns. Which do you want? If the sheet will be like the 1st example posted, I would start with a Template with the header and title row and the type row and use that formatting. If it is just the 2 columns like the 2nd example it would just as easy to format the information as it is created since it is only 2 cells at a time.


    Also, the Safety and Environmental type both have "abbreviations" with the categories (OHS Forms not Safety Forms and Enviro Forms not Environmental Forms) and Safety is on the WHS not OHS or Safety sheet. Do any of the other types use an abbreviation with the category and/or a different sheet name that must be hard-coded in?

    Steve

  7. #7
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Hi Steve - thanks for all your efforts so far. The first sheet is the one that is the actual working version, the second one i cut down.

    There are other abbreviations that will be used, basically one sheet needs to be created for each type.

    There is still some development work on the abbreviations so not sure yet how many and what the appreviated names will be

    I hope this help

    Cheers

    Dean

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Which version do you want the code to create, detailed example 1 or 2 col example 2?

    Are the abbreviations going to be in a worksheet (could you attach how they will listed) or should it be hard-coded in the VBA?

    Steve

  9. #9
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Hi Steve, Would.need to the code to created example 1

    I will send the other abbreviations to you later today.

    Thanks again for your help

    Regards

    Dean

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Let me know how the abbreviations will be stored: in a worksheet table of 3 cols: full name, abbreviation for list, abbreviation for sheet or do you want the VBA code to create the lists

  11. #11
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Steve - Please see list of Full Names and their abbreviations. These are stored in a tab and used in a Lookup to populate the respective cells

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I presume these are the worksheet names, Is this the listing name as well or do you want it fully listed in the "header" [For example do you want "Safety Forms" or "S Forms" [In your example you had "Enviro Forms" in the "Enviro" worksheet for Environmental and for Safety you had "OHS Forms" on the "WHS" worksheet.

    Which tab is the list in and where is it located. Could you post an example workbook with the "Doc Numbers" sheet, the abbreviation table, and an example template sheet for the output...

    Steve

  13. #13
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Hi Steve - please see attached zip file, I hope this helps.

    Thanks

    Dean
    Attached Files Attached Files

  14. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I am a little confused by your latest file and how it relates to the other ones. The new one seems to have 5 sheets of "doc Numbers". Do you want to extract from all of them with the code? How will the code know a sheet is a "doc number" as opposed to some other worksheet

    I did see that "Summary_Lookups" has a range named "Function_Type" and I will use that to lookup the name. Is that the abbreviation for the sheet name and the text or only the sheet name. The original examples had Environmental abbreviated "Enviro" and the Safety Type abbreviated "OHS" with Text and "WHS" for a sheet name.

    The current "output template" (WHS worksheet) has only 5 categories [The 3 single categories: Policies, Procedures, Forms, and the double category Plans & Manuals]. The previous examples also had a categorty called "Register" and the new doc Number sheets also include Specifications, SOPs, and a Safe Work Method Statement. Which categories are supposed to be extracted into the type sheets?. Also previously the category "Plans & Manuals" included the 2 categories "Management Manuals" and "Plans". The new example has a new category called "Manuals". Is this to be added to the "Plans and Manuals" so there are 3 columns to check for that Category?

    Steve

  15. #15
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Hi Steve - sorry about any confusion, this is a bit of a developing project.<br><br>I hope I can help to clarify your questions. At this stage I only need to summarise the info in the WA Region Doc Numbers tab and there needs to be on summary tab (ie like WHS) for each of the values in the in the Type Column (D) in the WA Region Doc Number tab and this needs to be divided into the respective document types ie POL, MM, PL, PR, etc down the tab.<br><br>So there need to be a tab created for each Type and then each of these tabs needs to have a section for each of the types of documents - that the POL, MM ets. The different subheadings can be what ever the sort of document it is.<br><br>I know this is abit confusing, I hope this help. But thanks very much for your persistence and assistance.<br><br>Regards<br><br>Dean

Page 1 of 2 12 LastLast

Posting Permissions

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