Posts by merz

    I am trying to expand this example to sum the values in the appropriate column between the specified dates.
    The lookup value, start and end dates are changeable


    I am currently using the following formula in the result box, but don't know how to limit the result to the specified date range.


    =SUM(INDEX($A5:$E17,0,MATCH($B$1,$A$5:$E$5,0)))


    I have attached a pic for more clarity.


    [ATTACH=CONFIG]69741[/ATTACH]


    Thanks for your help.


    Lookup Val ab Start Date End Date
    1/1/15 5/1/15
    Result 6419 =SUM(INDEX($A5:$E17,0,MATCH($B$1,$A$5:$E$5,0)))

    a ab abc abcd abcde
    1/1/2015 360 240 407 843
    2/1/2015 955 435 956 312
    3/1/2015 254 364 456 932
    4/1/2015 564 102 372 747
    5/1/2015 592 801 484 127
    6/1/2015 743 43 596 972
    7/1/2015 333 989 536 700
    8/1/2015 393 542 138 617
    9/1/2015 610 275 142 248
    10/1/2015 96 451 339 734
    11/1/2015 938 151 602 360
    12/1/2015 581 787 646 50

    Re: Reference Newly Added Sheet


    Dave I appreciate the reply but it is not what I am looking for.


    I am not having a problem creating a new sheet. My problem is referencing the cells in the new sheet through code. The code you sent creates duplicate sheets of the same name (MyNewSheet) - error.


    This part of the code works great

    Code
    Dim wsNew As Worksheet
     
    Set wsNew = Worksheets.Add(After:=Worksheets(Worksheets.Count))
    wsNew.Name = TextBox2.Value


    My problem is in this part of the code

    Code
    With Range("B65536").End(xlUp).Offset(0, 5).Activate
    ActiveCell.Formula = "=TextBox2.Value!AM6"


    I solved the problem


    Code
    ActiveCell.Formula = "=" & TextBox2.Text & "!" & "AM6"


    Thanks for your help

    I have a userform which creates a new sheet using TextBox2.value as the sheet name.


    Here is the code I am using and it works fine.

    Code
    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = TextBox2.Value


    Here is the problem - After additional code is run on the newly created sheet I need to take the value (Which will be text) from cell AM6 of the newly created sheet and place it on an activecell on Sheet3. Seems simple enough but I do not know how to address this new sheet as I do not know what the name will be.


    Here is the code I have tried (and various renditions of it)


    Code
    ActiveCell.FormulaR1C1 = "=TextBox2.Text!R[-9]C[32]"


    I do not want to specifically name the new sheet as many sheets could be created, unless you could tell me a way to do that.


    Any help would be appreciated,


    Thanks
    Ed

    Re: Conditional Formatting


    I got it solved


    I have change the cell's so you could place it on a sheet and see how it works, It uses a combination of a for - next loop, if statements and case.


    Place a simple formula in cells a1:a10 eg. A1= B1+C1


    Enter a Value in N1 and N2 50% 80%


    Then change any value in B1 thru C1 and watch it execute


    I think it is very cool!


    Thanks for everyone's help. It got me thinking!


    Here's the code


    Re: Conditional Formatting


    Appreciate your Reply Will,


    But Dave's Code works only when those specific cells in the range lose focus, It will not work when the value of the cells change.


    If you can tell me how to change his code so it will format when the value of the cells change rather than when they lose focus it would probably work for me.


    The cells I need to change will never recieve focus from the user - all are changed by formula.


    Thanks again,


    Ed

    Re: Conditional Formatting


    Simon,


    Please send an example.


    Anything I have tried, I cannot get it to fire on the Work_Change Event. When I place it under a cmdBtn to force it to fire. I can't seperate the cells The whole row turns color.


    Incidentally a cmdBtn would be useless in this case.


    Thanks for your Reply
    Your Help is greatly appreciated.

    Re: Conditional Formatting


    Here is the formula in each cell


    =IF($B$157=FALSE,ProbAT(IF($A$8="Long","A","B"),$H$8,$C$5,AJ$4,AU5,0,$J$7),ProbAE(IF($A$8=Long,"B","A"),$H$8,$C$5,AJ$4,AU5,0))


    The ProbAT,AE are complex probability functions used throughout the workbook, and I do not want to change the functions as I fear I could really fowl things up.


    Thanks for your Reply,
    Your willingness to help is appreciated.

    I am a newbe and I need some help!


    Cell B157 is changed by checkbox 1= TRUE, 0 = FALSE
    Cell H1 and Cell H2 are percentages changed by the user.


    Range(AI8:AQ8,AI12:AQ12,AI16:AQ16,AI20:AQ20, etc) each cell in the range contains a formula, and is protected from the user.


    I need the contents of each cell in the range to change font color (independent of each other) when the formula of any of the cells within the range changes - to the following


    If B157 = 1


    Cells < H1 (a percentage changed by user), would turn Red
    Cells > H1 but < H2 (a percentage changed by user) would turn Yellow
    Cells > H2 would be Green


    If B157 = 0


    Cells < H1 (a percentage changed by user), would turn Green
    Cells > H1 but < H2 (a percentage changed by user) would turn Yellow
    Cells > H2 would be Red


    I need it to fire whenever the value of one of the cells in the range changes.


    I have tried If statements, Select Case, and a combination of both but just can't get it!


    Thanks a lot, you help is appreciated!


    Ed