Posts by andyb16

    Re: Delete first "," (comma) from left in a alphanumeric string..


    Thanks apo :)!


    Basically my string is as below:


    ABC:5,622.871ms,Call:0ms,Stop:0ms,Halt:2,399.921ms


    When I want to delimit on , (comma) problem is that the numeric string contains commas which I want removed first.


    So your above formula worked great and my result now looks as below:



    ABC:5622.871ms,Call:0ms,Stop:0ms,Halt:2,399.921ms


    So comma in 5,622.871 is takem care of. In the same way can i remove first comma from right (for 2,399.921)?


    Thanks.

    Hi All


    I have a column with rows having strings as below (see forum.ozgrid.com/index.php?attachment/58397/excel attached) .


    I want to delimit the string in such a way that the for each parameter, the parameters will beome columns with headers and the value against them will be shown in thier respective column. The output tab in the attached excel might make what I am trying to convey more clear.


    Sp bascically CPU should have its own column and its time against it.


    There are thousands of such column that we wish to analyse and therefore the ask.


    I tried various option to delimit including recording a macro but did not work :(


    Hope someone ca help me or direct me here.


    Thanks!
    [TABLE="width: 208"]

    [tr]


    [td]


    String

    [/td]


    [/tr]


    [tr]


    [td]

    CPU: 5,622.871 ms, Sync: 0 ms, Wait: 0 ms, Suspension: 2,399
    .921 ms

    [/td]


    [/tr]


    [tr]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [/tr]


    [/TABLE]

    Re: Macro To Fetch Data based on 2 Criteria


    Thanks cytop!


    I think I have grasped the logic of the code


    Upon testing it line by line, I found that the below code is filtering out everything when it applies the criteria:


    Code
    .ListObjects("Table2").Range.AutoFilter Field:=1, Criteria1:=strMth


    Hence the resultant output is a blank sheet with header.


    I thought maybe this is because the worksheet object is missing in the below code :


    Code
    strMth = Range("I7").Value 
        strDir = Range("I8").Value


    But maybe I am wrong as that did not work either.


    Request your inputs.


    Thanks.

    Hi All,


    Attached is my .xls with dummy data forum.ozgrid.com/index.php?attachment/57013/.


    My intention is that once the Director and Month in tab 'By Director & Month' is chosen and the Fetch Data commandbutton is clicked, a new workbook should open.


    This new work book should have :


    • all entries in tab 'Monthly Invoice Input' for that Director-Month combination,
    • along with a sum total of column Q(Total invoice amount) in the last row.



    Is this possible?


    Thanks.

    Re: $15: Monthly Invoicing Excel : Partially built-need further help...


    Hi All,


    I did not get any response to my query.


    Which might mean:


    • Either the quote was too low.
    • OR The problem statement was not well articulated by me.
    • Or maybe both.



    I am willing to raise the quote to $15. Cannot go any higher as I cannot afford :(


    There is no fixed deadline as such...although it would be great if I anyone is able to help me with it by Wednesday.


    And further, I have trimmed the problem statement to be more specific and hopefully more lucid.


    Attached is my worksheet : forum.ozgrid.com/index.php?attachment/56993/


    The idea is to restrict the average of any person to 19 days.


    So in col N(Controlled Average) of Tab 'Monthly Invoice Input' , say Andy(TID:T11345) actually works for 19 days in April and 21 days in May. Then his average will be 20 days which is not acceptable. Since the average should be restricted to 19 , therefore the user should be restricted to enter a set of values which will maintain the average of 19.


    I had got a few good suggestions from our members on the free forum, but not quite what I wanted to achieve.



    Thanks.

    Hi Guys,


    I have paid 10% to Ozgrid....Unique Transaction ID 7U923233U0229431V. Deadline is 72 Hrs.


    Attached is a forum.ozgrid.com/index.php?attachment/56958/ which I am building.


    Below are the rules and logic based on which I want it to work. I have already been able to cover points 1 to 4 by using vlookups and index-match functions that I am familiar with. But need help on the remaining.


    I tried a little bit of VBA myself...but could not make it all come together.


    Rules
    1)In the 'Monthly Invoice Input' sheet, first month will be chosen.
    2)Next TID will be chosen
    Once TID is chosen, it should populate everything else in col's C to I against that TID from "Employee Records" tab
    3) Col K, 'Rate' should be calculated as per rates in "Rate Card" tab. Rate is a function of Role and Type.
    4) The rules for normalised days is that one resource can only work for a maximum of 228 days in a year(Financial year Apr-Mar) - or average 19 days a month
    Also, if actual days worked is 19 or below the accrued days(actual -normalised) cannot be utilised for averaging.
    5)Therefore, in the 'Normalised Days' col, the cell against each resource should provide a validation such that the dropdown should contain maximum allowable days to maintain an average of 19 days and also days below that number till 0. The actual days worked are in tab 'Days Worked In the Year'.
    Say for Example, in April I worked for actual 21 days. So my normalised days for Apr will be 19, and I will have an accrual in my name of 2 days(21-19) which can be used later to maintain the average.
    Now, say I avail a vacation in May and my actual Days worked for May is 17. Then I am allowed to bill only 17 days. I cannot use the earlier 2 accruals for averaging since I have worked less than 19 days.
    At this stage the 2 month average is 18(19,17). Now if I work for more than 19 days in June, say 21 days, then I am allowed to use this for averaging as per rule in 5).
    So in June I will have normalised days as 21, so average will be maintained at 19(average of 19,17,21).
    6)There are some people who work Partly on a Fixed Price(FP) model of billing and some others on Time and Material(T&M) -see tab Days Worked In the Year. While averaging their total days(FP+T&M) should be considered. Also, for months where there are 0 days worked, that month should not be considered for averaging.
    7) I want an option to supress the above averaging rule for extraordinary situations. So if in col M 'Supress Rule(Y/N?)', the flag is set at 'Y', the user should be able put in free text value.
    8)col D 'End Date' of 'Employee Record' Tab shows when the person was taken of a project. If end date is populated then his /her record upto that date should
    be preserved, but his/her entry in sybsequent month should not be allowed. An error msg shouls be displayed if entry is attempted.
    9)Net Invoice Amount(col P)= Normalized Days * Rate
    10)Total Invoice Amount = Net Invoice Amount + Over Time Pay + Other Charges
    11) There may be people who work on two or more projects. Their actual days should be aggregated to arrive at the average.
    12)In tab "Monthly Invoice- By Director", the combination of Director and Month should give an extract of these entries(pulling from "Monthly Invoice Input"). If "Display in New Workbook?" flag is Y, then same extract should open in new workbook.
    13) The 'Summary' tab has 2 tables a)Total by Director b) Total by contract. The running total for these should keep populating automatically.
    Also, if in table "Total by Director", the aggregate in "Invoiced Thus far" exceeds 'Total PO Amount', then an alert/warning should be displayed.



    Thanks.

    Hi All,



    First up, please note that this is a crosspost. I am crossposting because I have not received any reply on the other forum yet. The link to that post is:


    http://www.excelforum.com/exce…lt-need-further-help.html


    Attached is a forum.ozgrid.com/index.php?attachment/56950/ which I am building.


    Below are the rules and logic based on which I want it to work. I have already been able to cover points 1 to 4 by using vlookups and index-match functions that I am familiar with. But need help on points 5,6,7,8,11,12.


    Rules
    1)In the 'Monthly Invoice Input' sheet, first month will be chosen.
    2)Next TID will be chosen
    Once TID is chosen, it should populate everything else in col's C to I against that TID from "Employee Records" tab
    3) Col K, 'Rate' should be calculated as per rates in "Rate Card" tab. Rate is a function of Role and Type.
    4) The rules for normalised days is that one resource can only work for a maximum of 228 days in a year(Financial year Apr-Mar) - or average 19 days a month
    Also, if actual days worked is 19 or below the accrued days(actual -normalised) cannot be utilised for averaging.
    5)Therefore, in the 'Normalised Days' col, the cell against each resource should provide a validation such that the dropdown should contain maximum allowable days to maintain an average of 19 days and also days below that number till 0. The actual days worked are in tab 'Days Worked In the Year'.
    Say for Example, in April I worked for actual 21 days. So my normalised days for Apr will be 19, and I will have an accrual in my name of 2 days(21-19) which can be used later to maintain the average.
    Now, say I avail a vacation in May and my actual Days worked for May is 17. Then I am allowed to bill only 17 days. I cannot use the earlier 2 accruals for averaging since I have worked less than 19 days.
    At this stage the 2 month average is 18(19,17). Now if I work for more than 19 days in June, say 21 days, then I am allowed to use this for averaging as per rule in 5).
    So in June I will have normalised days as 21, so average will be maintained at 19(average of 19,17,21).
    6)There are some people who work Partly on a Fixed Price(FP) model of billing and some others on Time and Material(T&M) -see tab Days Worked In the Year. While averaging their total days(FP+T&M) should be considered. Also, for months where there are 0 days worked, that month should not be considered for averaging.
    7) I want an option to supress the above averaging rule for extraordinary situations. So if in col M 'Supress Rule(Y/N?)', the flag is set at 'Y', the user should be able put in free text value.
    8)col D 'End Date' of 'Employee Record' Tab shows when the person was taken of a project. If end date is populated then his /her record upto that date should
    be preserved, but his/her entry in sybsequent month should not be allowed. An error msg shouls be displayed if entry is attempted.
    9)Net Invoice Amount(col P)= Normalized Days * Rate
    10)Total Invoice Amount = Net Invoice Amount + Over Time Pay + Other Charges
    11) There may be people who work on two or more projects. Their actual days should be aggregated to arrive at the average.
    12)In tab "Monthly Invoice- By Director", the combination of Director and Month should give an extract of these entries(pulling from "Monthly Invoice Input"). If "Display in New Workbook?" flag is Y, then same extract should open in new workbook.
    13) The 'Summary' tab has 2 tables a)Total by Director b) Total by contract. The running total for these should keep populating automatically.
    Also, if in table "Total by Director", the aggregate in "Invoiced Thus far" exceeds 'Total PO Amount', then an alert/warning should be displayed.



    Thanks

    Re: EBIDTA Calc - Migrating From Formulas to VBA....Scripting.Dictionary Perhaps?


    I was trying to work with one of Jindon's code before I posted my question. But I am a newbie and could not get far. Since I received no response I have gone back to the drawing board and have started trying to built something again using scripting.dictionary again.


    I still humbly request if anyone can help me write a code or has any other approaches, please do advise.


    Thanks!