Posts by hamptongolfer11

  • Lowest 3 of last 7

    Quote from rory

    Your cells that look blank in those rows actually have spaces in - is there a reason for that?

    Actually no, I didn't realize there were spaces in them. ( And don't know how you knew it) but when I clear the blank cells it works perfectly. Thanks again for this. Much appreciated.

  • Lowest 3 of last 7

    Quote from rory

    One way:

    =AVERAGE(SMALL(INDEX(B4:R4,LARGE(IF(B4:R4>0,COLUMN(B4:R4)-1),MIN(7,COUNT(B4:R4)))):R4,{1,2,3}))

    array-entered (Ctrl+Shift+Enter) into S4, then fill down.

    Thanks Rory for your help. I am quite new to excel. I tried copying and pasting the formula but that came up with a value error. I am not sure what you mean by array-entered (Ctrl+Shift+Enter). Is it possible you could attach the file with formula entered. Thanks again.

  • Help with formula

    Thanks Bosco, that works perfectly. Thanks also to Roy and Mario for their help.

  • Invalid extension

    I am trying to post a question in the Formulas forum but when I try to attach the file it says extension not valid. It is a .xlsx extension which I have attached before. What am I doing wrong?

  • Count Highlighted Cells

    Quote from Justin Doward

    You can replace the code in the workbook_SheetChange event with:


    Code
    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal target As Range)
If Not Intersect(Sh.Range(target.Address), Sh.Range("C7:U41")) Is Nothing Then
Application.ScreenUpdating = False
Call background_color_count
Application.ScreenUpdating = True
End If
End Sub

    Thanks again Justin, put it in and total updates automatically. Appreciate your help.

  • Count Highlighted Cells

    Quote from royUK

    I've had formulas in Div 4, you could do the same in the other sheets then summarise the results.

    Hi, Roy, thanks for your help. I am not following the logic of your formulas. It appears you are summing the cells with a 6, 7 and then all other cells. I don't see how that will give me a count of the green highlighted cells. Help.

  • Count Highlighted Cells

    Quote from Justin Doward

    The function would be a better solution, rather than having to run the macro every time. Either that or writing the code into the sheetchange event so it updates whenever the conditional format is activated. But if the macro does what you need all is good :)

    Hi Justin, I went back and checked and you did have the correction for the DisplayFormat in the macro you sent. However, do you have an example of the function I could use so not have to use the macro? Thanks.

  • Count Highlighted Cells

    Thanks Justin, I will try that. What you gave me does seem to work though.

  • Count Highlighted Cells

    Fantastic! Thanks Justin.

  • Auto move to next cell

    Thanks Rollis for all your work. The example you attached works exactly as I want it to. I will have to try and find a work around to use the keypad. I am 5 times slower using the # keys than the keypad. Appreciate your effort.

  • Auto move to next cell

    Quote from tr1face

    Hey Hamp,


    Until you're pressing Enter you're still gonna be in Edit Mode, so I don't think it's possible.


    You could use another cell on your sheet to enter one long number which consists of all your data, then split it up into single digits, listing them in desired rows

    This may work but I am not sure of the process to "split it up into single digits, listing them in desired rows". I am fairly new to macros.