Posts by merz

Re: Use Hlookup to find then sum column within specific dates

How do i close this thread. I figured it out

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

OH!

Now I get it - thanks.

I am glad you explained that.

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

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

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

I solved the problem

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.

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)

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

Private Sub Worksheet_Calculate()


Dim rMyCell As Range
Dim icolor As Integer


For Each rMyCell In Range("A1:A10")
If Range("H2") = 1 Then
    Select Case rMyCell.Value
        Case Is <= Range("N1").Value
            icolor = 4  '4 = Green
        Case Is >= Range("N2").Value
            icolor = 3  '3 = Red
        Case Range("N1").Value To Range("N2").Value
            icolor = 6  '6 = Yellow
    End Select
        
Else
    If Range("H2") = 0 Then
    Select Case rMyCell.Value
        Case Is <= Range("N1").Value
            icolor = 3  '3 = Red
        Case Is >= Range("N2").Value
            icolor = 4  '4 = Green
        Case Range("N1").Value To Range("N2").Value
            icolor = 6  '6 = Yellow
        End Select
    End If


     
End If


    rMyCell.Font.ColorIndex = icolor
    
Next rMyCell
      
End Sub

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