Posts by Webbers

    Hi all!


    Thanks in advance for your help. I have designed a project tracker with a Gantt chart in Excel. Now I have one issue that I simply cannot figure out how to correct, and I have searched the forum here as well as I have Google'd for an answer without success. Now in my example attached, there are 4 projects listed (1-4). And each project has 4 tasks associated with it. There are multiple colors on my chart, and I like this visual as it makes everything easier to read (my real template has 67 projects that must be tracked, so this is very important). Okay now each project and the associated 4 tasks should be in the same color. I have highlighted in yellow on my spreadsheet example the task(s) that show an incorrect color based on the project the task is associated with.


    For example
    [TABLE="border: 1, cellpadding: 1"]

    [tr]


    [td]

    Row #

    [/td]


    [td]

    Project/Task Name

    [/td]


    [td]

    Notes

    [/td]


    [/tr]


    [tr]


    [td]

    10

    [/td]


    [td]

    1.4 Implementation

    [/td]


    [td]

    The chart shows this bar in green, however, as it is part of project 1, it should b e blue like the rest of project 1.

    [/td]


    [/tr]


    [tr]


    [td]

    14

    [/td]


    [td]

    2.2 Development

    [/td]


    [td]

    These 3 tasks are part of project 2, so the data bars should be in green. However they show in grey.

    [/td]


    [/tr]


    [tr]


    [td]

    15

    [/td]


    [td]

    2.3 Testing

    [/td]


    [td]

    These 3 tasks are part of project 2, so the data bars should be in green. However they show in grey.

    [/td]


    [/tr]


    [tr]


    [td]

    16

    [/td]


    [td]

    2.4 Implementation

    [/td]


    [td]

    These 3 tasks are part of project 2, so the data bars should be in green. However they show in grey.

    [/td]


    [/tr]


    [tr]


    [td]

    22

    [/td]


    [td]

    3.4 Implementation

    [/td]


    [td]

    This task is part of project 3, so the data bars should be in purple. However it shows in red.

    [/td]


    [/tr]


    [/TABLE]


    Now, beginning with project 4, everything shows in red. Ideally I would like to be able to "rotate" though a group of colors. i do not expect to be able to use unique colors for each project. Ideally I would like to be able to have this set so it rotates though 5 or 6 colors. And please tell me HOW to do this in my master file where I have 67 projects listed already.


    Thanks again!

    Please Note that I provided 2 examples in my log. The current range as I mentioned needs to become slightly larger and also most importantly SKIP column Q. Column Q data is generated from a pull down menu consisting of only about 8 choices, and people often choose incorrectly. I need my analysis of this data to be driven by the search parameters.


    Row 2 data pulls correctly. "Human Error" is listed in column R, and displays in column AM
    Row 3 data pulls incorrectly. "Human error" is listed in Column Q (the drop down from the source), but it displays in column AM although those keywords nor nothing else from the keyword list actually appear in this row. If you change the data in column Q you will notice that column AM no longer populates.


    I need to make sure column Q is excluded from my search range. Thanks!

    I Need to search multiple ranges for keywords & phrases within text strings. These text strings are basically paragraphs of user notes. Now I had a formula that worked perfectly (and I was so proud of myself) until I was advised by my boss that we could not include Column Q within the search criteria. The old and new ranges along with the criteria I am using is listed below, along with my functional formula. I would think I could simply add an AND/OR and list the formula twice with each of my new ranges, however, if that is what I need to do, I am having difficulty making that function properly.


    [TABLE="border: 1, cellpadding: 1, width: 500"]

    [tr]


    [td]

    OLD Range

    [/td]


    [td]

    $A2:$AG2

    [/td]


    [/tr]


    [tr]


    [td]

    NEW Range 1

    [/td]


    [td]

    $A2:$P2

    [/td]


    [/tr]


    [tr]


    [td]

    NEW Range 2

    [/td]


    [td]

    $R2:$AI2

    [/td]


    [/tr]


    [tr]


    [td]

    Range for keyword list

    [/td]


    [td]

    error

    [/td]


    [/tr]


    [tr]


    [td]

    Display if TRUE

    [/td]


    [td]

    Human Error

    [/td]


    [/tr]


    [/TABLE]


    =IF(SUMPRODUCT(--ISNUMBER(SEARCH(error,$A2:$AG2))),"Human Error","")

    Re: Upgrade existing macro to send data from multiple rows in one email


    mrmmickle1----


    I am having a few issues....


    I placed the Worksheet_change code on Sheet 1, and it works, however I tried something... if I fill the form out correctly (the first time), entering the DM in column F, and then entering Yes in column G, I am still prompted to enter the DM name, which I already entered. and if I just cancel it (since I already entered it, then the change event overwrites the name that previously existed with "FALSE".


    Also, the macro appears to ignore "yes" (lowercase) as opposed to "Yes" (Titlecase). I have no control over the end user, and I need all variations (uppercase too) to be accepted, as long as the response is Yes.


    The macro revision, now includes the DM column, this is great! however it still does not compile these entries to be sent in 1 email listing everything, it looks like they are being sorted, and 1 email is being generated for each DM name... I meant 1 email total with all records. So you understand, this email is being sent to the development team so changes can be made, so the actual; DMs do not get the email. Sorry about the miscommunication on that point.


    And again, thanks so very much for all your help on this code, it is greatly appreciated!!!!

    Re: Upgrade existing macro to send data from multiple rows in one email


    mrmmickle1----


    I tried your file, now the sort, it can certainly be re-sorted by the end user so they can find records next time. However your script does not include column F results at all (DM), which is critical and the whole purpose of this email. Plus I want to be able to alert userts that they have records that do not contain a DM. the file you posted has completed records on rows 2-9, and then rows 10-11 contain a name not a YES in column G... this is unlikely to happen, but the script is made to trigger based on YES in column G as the primary criteria, and then a name in F as a secondary criteria. If Yes but no name, the user should be prompted. And all records must be sent in one email. I have no objections to the "table format", personally I just had no clue about that. I can live with the Outlook "nag" screen if forced too... or rather make the end users live with it! LOL

    Re: Upgrade existing macro to send data from multiple rows in one email


    The Outlook pop-up is only 1 of my challenges, and actually the least of them. For this, option 1 ("Never Warn Me About Suspicious Activity") is unrealistic, and this is due to the number of users... most of which I have no clue who they are, and the constant turnover of users. Option 2 is possible, but I would have to be able to incorporate the rest of my existing VBA script into your example. Knowing 0 about this CDO method I am not exactly sure how to do that. Plus when the email is sent I require it to generate ONLY 1 email. If there are more than 1 row of data that meets the 2 criteria mentioned, their data would also be listed in the same email. But if I had to choose which I am willing to give up I would choose the anoying pop-up message from outlook and have all other aspects work correctly

    I have a working VBA script, but I require it to do more than it currently does. This script is based on criteria in column G as a trigger ("Yes" is required) and a name is required in Column F for any corresponding Yes in column G. My sample shows Yes in 3 rows (2, 9, and 13), however the way this macro is currently working it sends 2 seperate emails (to the same address hard coded in the VBA) rather than listing all info in one like I would prefer. In addition, I would like to ensure that any variation of "yes" will still cause this trigger, this is NOT case sensative, and there are all different end users that will fill this out. Also for instances like row 13 where Yes is listed in Column G but there is NOT a name listed in column F to correspond, I would like a message box to appear giving the user the option to add the name or send the emails (message box: "The DM is a required field for any new clients. Would you like to add the DM now or send the other updates now? Add DM / Send Now" (obviously if a row number could be referenced for this that would be great, but not a requirement"


    Column G = Yes
    AND
    Column F = NOT blank


    Also there is a pop-up message that appear when I attempt to send a message using this current macro. I want to avoid having this message appear, as I have no control over the end users. I am attaching a sample file which contains my existing working macro that simply requires an "upgrade" and also a screen shot of that pop-up message that I want to avoid seeing, or having my users see. Thanks in advance for all your help it is greatly appreciated!

    Re: Extract data between 3rd instance of character and a space


    Quote from Batman;734648

    There might be a shorter way to do it, but this should work:


    =MID(D3,FIND("@",SUBSTITUTE(D3,"\","@",3))+1,FIND(" ",D3,FIND("@",SUBSTITUTE(D3,"\","@",3)))-FIND("@",SUBSTITUTE(D3,"\","@",3))-1)


    woooohoooooo! Works perfectly! Thank you so very much!!!!!!

    I need to create a formula that will extract a part of another cell value within a path. The portion I need to extract is only the number (in the example below: 123456).


    Q:\2014 Stuff\(12) December Stuff\123456 Smith 2nd check


    Variables:

    • The number can be anywhere between 4-15 characters long.
    • The path will alter slightly (years and months)


    Constants:

    • Path format the same (Drive, then year folder, then month folder)
    • Which means that the number I need to extract will always be after the 3rd instance of "\" and before the next space.



    I was able to partially do what I need to... the formula below extracts the text after the 3rd instance of the "\", however, it gives me "123456 Smith 2nd check" rather than only "123456"


    =TRIM(LEFT(SUBSTITUTE(REPLACE(D3,1,FIND("@",SUBSTITUTE(D3,"\","@",3)),""),"\",REPT(" ",100),1),100))



    Any help is appreciated. And thanks in advance for all your time and assistance.

    I have a spreadsheet and I need to be able to create a macro that will do the following:


    create a new subfolder from the value in cell I25 ("info" tab)
    The folder needs to be created in an existing directory from the value in cell O8 ("info" tab)
    The file needs to be saved in the subfolder just created with the file name that is listed in cell J32 ('info" tab)


    The file name (listed in cell J32) already includes the file extension of ".xlsm", however if it makes things easier, I can remove the extension from the cell. These files will always be saved as ".xlsm" files. below is a table listing the location of the cells where the data will be accessed for this macro, each of these 3 cells resides on a worksheet named "info". The file used will be a template and then when used each week the macro will save the template as a name name with all the proper formatting (this is critical as we need to link back to previous files)


    [table="width: 1000, class: grid, align: left"]

    [tr]


    [td]

    cell I25 - Subfolder name

    [/td]


    [td]

    05.25.2012

    [/td]


    [/tr]


    [tr]


    [td]

    cell O8 - existing directory

    [/td]


    [td]

    X:\Compensation\Hours and Earnings\LV Payroll\BWJ\2012 Payroll\VC Payroll Worksheets 2012\

    [/td]


    [/tr]


    [tr]


    [td]

    cell J32 - file name (with extension)

    [/td]


    [td]

    05.25.2012 VC Las Vegas.xlsm

    [/td]


    [/tr]


    [/table]

    Re: IF Statement with VLOOKUP


    Okay, I believe it is working correctly now. This is what I came up with:


    =IF(VLOOKUP($A757,TeamList_Table,26,FALSE)="",VLOOKUP($A757,TeamList_Table,9,FALSE)*(VLOOKUP($A757,TeamList_Table,13,FALSE)),VLOOKUP($A757,TeamList_Table,13,FALSE)*(VLOOKUP($A757,TeamList_Table,26,FALSE)))

    Re: IF Statement with VLOOKUP


    Well, I kept messing with the formula, and I found that when I removed the IF statement at the befginning, it would work! But I was bound and determined to put it back in, and I got it!!! I win!!!! The revised formula is:


    =IFERROR(IF($A757="-","-",IF(VLOOKUP($A757,TeamList_Table,26,FALSE)="",VLOOKUP($A757,TeamList_Table,9,FALSE),VLOOKUP($A757,TeamList_Table,13,FALSE)*(VLOOKUP($A757,TeamList_Table,26,FALSE)))), "-")


    nope, this is NOT working. I thought it was but I just tested it with a few scenerios and it is not working properly.

    Okay, I am stuck, I had a formula, that worked just perfectly, then I had to add another criteria to it, and that is where I am stuck. Basically I want the formula to check Column 26 of the "TeamList_Table", if that cell is blank, then I want to use the value in column 13 and multiply by the value (a percentage) in column 9. However if column 26 is not blank, then I want the percentage in column 26 multiplied by the value in Column 9 from my Vlookup. Any ideas??


    =IFERROR(IF($A757="-","-",IF(VLOOKUP($A757,TeamList_Table,26,FALSE)="",VLOOKUP($A757,TeamList_Table,9,FALSE)*(VLOOKUP($A757,TeamList_Table,13,FALSE),(VLOOKUP($A757,TeamList_Table,26,FALSE)*(VLOOKUP($A757,TeamList_Table,13,FALSE), "-")))))

    Re: Macros to Protect/Unprotect Worksheets and Workbooks


    Luttrrt----


    Just wanted to say thanks for the VBA macros. I am doing project work at my new job, so I am giving the existing spreadsheets a complete facelift and then automating almost everything. Before (I arrived), formulas were constantly getting messed up due to an innocent error by the end user. You macros allow me an easy way to protect them, and then unprotect them when I need to make adjustments. Thanks again, these scripts are awesome!

    Morning all! Okay, I have seen many possibilities for my "challenge", but there is always at least one component that I cannot get adapted to resolve this.


    Goal:
    To compare two columns of data, which is on different sheets. The workbook attached provides a sample of what I am trying to accomplish... get a list of Employee IDs that appear on the "source data" tab (which can vary in length each time it is run) that do not match the list of the employees on the "staff" tab.


    Variables:
    "source data" tab will vary in number of rows. Employee IDs are in column C on this tab. "staff" tab is a fixed number of rows, the employee IDs are listed in Column B, from rows 2 - 26. The "source data" will have duplicate entries of the staff, this is fine. The "source data" will also have 0 listed, I want 0 to be excluded from the final list of non-matching items. This file name and path will change each time, so if a macro is used (I have no preference of macro or formula), than the macro will need to be based on the fact that it is the active workbook.


    Display results:
    I would like to list ONLY the unique employee number from the "source data" tab to appear on the "staff" tab beginning at row number 42 in cell C42. All additional non-matching numbers will show in the rows below (all in column C), with no blank rows between them. So I will be left with a list of only a handful of employee numbers. I have no preference to acheiving this result by use of macro or formula, whichever is easier.


    Example results
    In the attached spreadsheet I provided, there are 5 employee numbers that are unique to the "source data" tab, which are: 0, 2379, 2058, 3405, and 3569. But since 0 is to be excluded from the list, it should appear as follows. I have provided a table with the cell references each would appear in to provide a "visual". Thanks in advance, your help is greatly appreciated!


    [table="width: 200, class: grid"]

    [tr]


    [td]

    Cell C42

    [/td]


    [td]

    2379

    [/td]


    [/tr]


    [tr]


    [td]

    Cell C43

    [/td]


    [td]

    2058

    [/td]


    [/tr]


    [tr]


    [td]

    Cell C44

    [/td]


    [td]

    3405

    [/td]


    [/tr]


    [tr]


    [td]

    Cell C45

    [/td]


    [td]

    3569

    [/td]


    [/tr]


    [tr]


    [td]

    Cell C46

    [/td]


    [td][/td]


    [/tr]


    [/table]