Posts by Royzer

    Re: Formula to extract variable substring from string with multiple spaces


    Quote from StephenR;619217

    Can you clarify where the spaces are? Those two examples look pretty much identical. Is the number always after the hyphen - do you mean there variable number of spaces between the hyphen and the number?


    Sorry. There will always be the exact same number of characters and spacing leading up to the first numerical character.

    Hi.


    I've found several posts about returning variable substrings that dealt with one or two spaces, but I have not been able to find anything that is for multiple spaces. The number of characters before the substring will remain constant.


    For example:


    Income from transmission agreement - 83 subs @ $0.44


    In this case I need to extract: 83


    Income from transmission agreement - 10,312 subs @ $0.50


    Need: 10,312



    I would really appreciate any help you can give me.


    Thanks!

    I have run into a big problem. I created a file in Excel 2007 that uses a SUMIFS formula and works perfectly. BUT it turns out that the users of the file have Excel 2003 and it doesn't work for them. Is there any way to convert this formula to one that will work in Excel 2003?


    =SUMIFS(RDC!$C:$C,RDC!$A:$A,A9,RDC!$B:$B,"*"&$P$1)



    Thank you! :smile:

    I received this formula in answer to a question a few days ago, and it works perfectly. But I am curious about the "*" part of the formula. I have Googled it and cannot find a reference to it.


    I use this to look up an account number in column B whose last two digits match the value in $P$1.


    =SUMIFS(RDC!$C:$C,RDC!$A:$A,A9,RDC!$B:$B,"*"&$P$1)


    What is the asterisk in quotes "saying"?


    Thanks!


    :smile:

    (SOLVED)


    Hi. I have searched and tried everything I can find about using multiple criteria to return values, but I have not found anything that will work for my project. I've attached a workbook to show what I'd like to be able to do, and I'd really appreciate any help you could give me.


    thanks! [Blocked Image: http://www.excelforum.com/images/smilies/smile.gif]forum.ozgrid.com/index.php?attachment/46080/


    **Edited to show solution**


    =SUMIFS($K:$K,$I:$I,$B20,$J:$J,"*"&C$18)

    Re: Allocate monthly costs to the appropriate months based on date range


    Quote from CorvetteLover;610320

    this can be accomplished with a simple if statement that can be copied into all of the cells

    Code
    =IF(AND(E$1>=$B2,E$1<=$C2),$D2,"")


    I'm sorry, CorvetteLover, but none of the cell addresses in your formula correspond with the data cells in my spreadsheet and I'm having trouble following what I'm supposed to do.

    Re: Allocate monthly costs to the appropriate months based on date range


    Quote from venkat1926;609193

    there is a facility in this newsgroup to attah a workbook. why don't you do that and explain so that it will be helpful to help. you can use random data for security reasons.


    Sorry it took me so long to respond. I've been out of town all week. I've attached a workbook as you suggested. Thanks!

    I need Excel to look at a date range, then put the (pre-determined) monthly amounts into the correct cells beneath each of the Month-titled columns.


    Source Data, for Example:


    Contract A is from 01/01/12 - 12/31/12 for $1,000/mo


    Contract B is from 03/01/12 - 02/28/13 for $1,500/mo



    I need Excel to identify each calendar month in the date range and put the fixed amount in each column where the heading matches one of the months in the date range.


    The result I need would look something like this:


    [TABLE="width: 500, align: center"]

    [tr]


    [td][/td]


    [td]

    Jan-12

    [/td]


    [td]

    Feb-12

    [/td]


    [td]

    Mar-12

    [/td]


    [td][/td]


    [td]

    Apr-12

    [/td]


    [td]

    May-12

    [/td]


    [td]

    June-12

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    [TABLE="align: center"]

    [tr]


    [td]

    Contract A

    [/td]


    [td]

    01/01/12

    [/td]


    [td]

    12/31/12

    [/td]


    [td]

    $1,000/mo

    [/td]


    [/tr]


    [/TABLE]

    [/td]


    [td]

    $1000

    [/td]


    [td]

    $1000

    [/td]


    [td]

    $1000

    [/td]


    [td][/td]


    [td]

    $1000

    [/td]


    [td]

    $1000

    [/td]


    [td]

    $1000

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    [TABLE="align: center"]

    [tr]


    [td]

    Contract B

    [/td]


    [td]

    03/01/12

    [/td]


    [td]

    02/28/13

    [/td]


    [td]

    $1,500/mo

    [/td]


    [/tr]


    [/TABLE]

    [/td]


    [td][/td]


    [td][/td]


    [td]

    $1500

    [/td]


    [td][/td]


    [td]

    $1500

    [/td]


    [td]

    $1500

    [/td]


    [td]

    $1500

    [/td]


    [td][/td]


    [/tr]


    [/TABLE]


    I'd really appreciate any help you can give me. Thanks!

    Re: VBA-Insert row and autofill in protected sheet


    Adding the code:

    Code
    ActiveSheet.Protect Password:="cash", UserInterfaceOnly:=True

    at the beginning solved the issue:


    Re: VBA-Insert row and autofill in protected sheet


    To clarify the question above (since I ran out of time to edit the post):


    On DoubleClick the code above inserts a row and also auto fills formulas in the newly inserted cells of several columns in my worksheets. The range of formulas has been protected and now I need to have the code Unprotect the sheet on double-click and then Protect it again after the autofill/copy down to the new row happens.


    Thanks! :)

    Hi.


    The code below inserts a row and also auto fills formulas in the newly inserted cells of several columns in my worksheets. I would like to be able to protect the columns that contain the formulas but still have the code be able to insert rows and autofill the formulas into the new row using double-click. Is there a way to do this?




    Thanks! :smile:

    Hi. I have a wb with 20 sheets. I need to copy the data ranges from the first 17, but not the last 3, "Create File", "Pull" and "Save as DIF". The data will be pasted on a new sheet named "Target" created by the code. The code below does everything I want it to EXCEPT it also copies and pastes the three sheets above. How can I modify the code to exclude these three sheets?


    Thank you.