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

    Help with Code or Forumla

    I need some help with either a formula or code for the attached speadsheet sample
    Based on the value in D8 in the PD template tab I need to be able to populate the cells (in the red boxes I've drawn) starting at C22 of the PD template where the value matches the Job title in the Task Tab-the vlookup formula works fine just need to be able to extract the related numbers in column a in the task tab. The same applies for the measurable task section of the PD template but originating from the Responsibilities & Duties tab.
    This all works fine if I filter the Job title columns in both the Task and Role & Responsibilities tabs then copy and paste the values into the PD template, so basically I would like to automate this process.
    Would also be nice (if itís possible) that the number rows in each section could adjust to match the number of values - so row 46 and 47 would move up and down based on the number of matching tasks in the section above and also if a code is the only or best option then can it run as soon as the value in D8 template tab?
    I hope all that makes sense and is not asking to much.
    Thanks for any assistance
    Last edited by verada; 2013-01-20 at 16:36. Reason: Removed incorrect attached file. new attachment in reply

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 645 Times in 589 Posts
    Please take a look at the file you uploaded and make sure it is the correct one. What you are describiing does not jive with what is displayed

  3. #3
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Opps - sorry you are correct. Please see this file, should make more sense now.Thanks
    Attached Files Attached Files

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 645 Times in 589 Posts
    Verada,

    Your workbook is very organized and neatly contructed. It was a pleasure to work with. Your explanations were very precise and it made it very easy to follow what you wanted to achieve.

    Take a look at what I came up with. Much of your VLookup formulas were replaced with VB code except for the very top section. The spin button still controls the Project title and both the second and third sections are filled with the appropriate tasks and responsibilities. The rows will adjust to the size of each section as not to leave blank rows.

    There is no need to filter data as the code pulls data (records) based on the postion title as the index key. A small database in the shape of a spreadsheet. Nicely done!

    Note: There are hidden values in Column A. The Tasks, Resposibilities & Duties, and Reporting Structure Sheets were not altered.

    I hope this meets your needs.
    Maud
    Attached Files Attached Files

  5. #5
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Hi Maud - thankyou very much for the complements and your assistance

    What you have done looks very close to what I need. Just acouple of things.
    The Position Titles will expand to those I have included in the Sample, so rather than have the position titles hard coded in the macros it needs to be related to the value in D8,
    Also need to have the values for "All personnel" included in the measurable tasks section of the DB template (perhaps there could be a "Yes/No" option under the spinner),
    Is it possible to have the Print Area adjust to match the depth of the rows in the DB template?

    Thanks again for your assistance

  6. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 645 Times in 589 Posts
    Verada,
    When you click the spin button you have linked cell H7 while D8 is filled with the Position Title. The code presented matches D8 to fill both lower sections. The "All Personnel" is included in the Measurable Tasks for each of the positions you have provided. As you have mentioned, you will be adding additional positions and that is where a database has its advantage. Although we are working through Excel, it can be done with additional coding. I'll give it some thought. In the meantime, other members may come up with the formulas you need. We can still separate the code to adjust the number of rows however.

    Maud

  7. #7
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Maud - thanks very much again for your help and patients.

    Also when you give this some more thought, the responsibilies & duties and Tasks assocoated with each Job title may not alway be in sequential order and may grow or shrink as required - as you say this is the database function.

    With out knowing enough to be able to write the code, I used the manual sort and cut and paste manual method to get the result I was after, this is why I used the vlook's based on the sequential number in colum A of both the tabs - if this can be automated using code that would be great and incorporating the adjustment for the number of rows in each section and print area, that would be even greater
    Regards

    Verada

  8. #8
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Hi Maud (or any other members) - Any luck with the additional coding (or formulas) that would be needed to progress this? I've got a bit of pressure on me to get this going, so any other thoughts/assistance, etc would be very much appreciated. Thanks in advance. Verada

  9. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 645 Times in 589 Posts
    Verada,
    I have written in the code to filter all the unique job titles placed in the Tasks sheet in the B column. That list will populate a dropdown box that will replace the spinbutton on the PD template. It is dynamic in that you can add any new job titles and tasks in any order and they will be found. The rest will work the same with the 3 sections and perspective resizing. Navigation with a dropbox will be much faster than toggling between many items. Will post when completed

  10. #10
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Maud - thats sounds great - thanks very much for all your efforts. Look forward to seeing to seeing it.

    Regards

    Verada

  11. #11
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 645 Times in 589 Posts

    99.9% there

    This revised edition lets you add job titles, tasks, and responsibilities and will adjust automatically. Can't seem to work out one small bug and that is it repeats the last task or reponsibility when it rebuilds the sections. I'll figure it out though. Continue to add to the reporting structure sheet. It does not affect anythig that I did.

    Maud
    Attached Files Attached Files

  12. #12
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Maud - That looking really good. Will have a good play with it to test it. Thanks again for your efforts so far - very much apprecicated

    Regards

    Verada

  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 Maud - any luck with the 00.1%?

    I've had a good go at your revision and it works very well. Just the issue (that you are are of) of the duplicated rows at the end of each section.

    I've tried to sort out the print area based on the depth of the cells using "=OFFSET('PD Template'!$C$5,0,0,COUNTA('PD Template'!$C$5:$F$2000)+11,COUNTA(PD Template!$C$5:$F$5))". Pretty close but only selects colum C when it prints - any thoughts on where the problem in the formula might be and what needs to be changed to get the width correctly?

    Thanks for your assistance

    Cheers

    Verara

  14. #14
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 645 Times in 589 Posts
    Verada,
    I can easily code the print area. Please post a file with a box drawn around whatever you want to be printed. Have you tried adding additional job titles via Tasks (this is what it populates the combobox from), responsibilities, or reporting structure? Will complete the 0.1% (minor) at first opportunity as I am also helping others with projects.

    Maud

  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 Maud - basically the print area needs to go from the top section of the spreadsheet that contains the "Position Details for....." down to the last row that contains text at the end of the Measurable Targets section.

    Yes I have tried adding addtitional titles and all appears to be working extemely well, based on the tests I have done so far (excellent job).

    I'm know your assistance is much appreciated by others also and also myself - thanks

    Regards

    Verada

Page 1 of 3 123 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
  •