Excel gurus - rolling 365-day formula

wesimmo said:
No, you can reference another sheet in the range part of the formula, a custom function works just like an inbuilt function in that respect.

So I've just written a little custom function that counts non blank cells, called ws.

I've then got a sheet of data called other_sheet and then a 'report sheet'.

In the report sheet I can put
=ws(other_sheet!A1:A6)
and it will count the non blanks in that range on that sheet.
Click to expand...
I tried this but it doesn't work for me using this formula =Occurrences(A:A,E:E,"A")

Or do I have to start with A1 to the end I wonder rather than the entire column?

I tried on sheet 3 to do the same =Occurrences('Staff Absence'!A:A,'Staff Absence'!E:E,"A")

But both sheets give me an error. On the same sheet it shows #VALUE! (former) and on sheet 3 (latter) it gives me #NAME?
 
Last edited:
Yella Fella said:
I tried this but it doesn't work for me using this formula =Occurrences(A:A,E:E,"A")

Or do I have to start with A1 to the end I wonder rather than the entire column?

I tried on sheet 3 to do the same =Occurrences('Staff Absence'!A:A,'Staff Absence'!E:E,"A")

But both sheets give me an error. On the same sheet it shows #VALUE! (former) and on sheet 3 (latter) it gives me #NAME?
Click to expand...

The value means that the custom function is recognised but there must be something wrong with it or the data type maybe.

The name means it doesn't recognise the function, have you moved it to module1 and not kept in in Sheet1?
 
wesimmo said:
The value means that the custom function is recognised but there must be something wrong with it or the data type maybe.

The name means it doesn't recognise the function, have you moved it to module1 and not kept in in Sheet1?
Click to expand...
Yeah, it's already in Module 1 and not in a specific sheet. The weird thing is that the original code I copied works on all 3 sheets. But if I try to reference another sheet it fails.
 
gpuerrilla said:
When I discovered Power Query circa 2017 it meant less VBA and less formulas. Not sure if your using any dynamic data from other sources but well worth learning as its a decent tool to not only tidy up your data but also sidestep vlookups and start using M/DAX which is really powerful.
Click to expand...
I only ever used this for Power BI dashboards but alas, this is more an Excel function I feel.
 
gpuerrilla said:
Was thinking on the lines of keeping it structured. Have a table of your staff, sickness types etc. Much easier to update that then have your calculations done in the Data model. Each to their own! :)
Click to expand...
I do for the normal staff rota... but have to work out the Bradford Factor score across the board ad-hoc when asked. Saves time doing it manually I always say.
 
Yella Fella said:
If it helps... I did have a VB script to work out the occurrences which does work on one test sheet but I can't get it to work on the sheet I actually want to report in :cry:. Not sure if it has to be written or put into a particular sheet/module. New to the coding side:

Code: 
Option Explicit

Function Occurrences(daterange As Range, coderange As Range, code As String)
    Dim i As Long
    Dim f As Boolean
    For i = 1 To daterange.Count
        If Weekday(daterange(i).Value, vbMonday) <= 5 Then
            If coderange(i).Value = code Then
                If Not f Then
                    Occurrences = Occurrences + 1
                    f = True
                End If
            Else
                f = False
            End If
        End If
    Next i
End Function
Click to expand...

Rather than using VBA you can use a LAMBDA function in the name manager:

aFlNabg.png

Go to name manager > Create a new name called "Occurrences" > Paste the following in 'Refers to:'

Rich (BB code): 
=LAMBDA(Date_Range, Code_Range, Code,
    LET(
        RemoveBlanks,           IF(Code_Range = "", ":", Code_Range),
        FilterWeekdays,         FILTER(RemoveBlanks, WEEKDAY(Date_Range, 2) <= 5),
        RemoveConsecutiveDays,  TAKE(
                                    SCAN(
                                        TEXTJOIN(, , FilterWeekdays),
                                        SEQUENCE(100, 1, 0, 0),
                                        LAMBDA(a, b, SUBSTITUTE(a, REPT(Code, 2), Code))),
                                    -1
                                ),
        LEN(RemoveConsecutiveDays) - LEN(SUBSTITUTE(RemoveConsecutiveDays, Code, ""))
    )
)

This formula could probably be tidied up a bit but it seems to work ok. (EDIT: Improved it a bit. No more self referencing.)

I've written it so the formula inputs are the same as your VBA UDF:

Code: 
=Occurrences(Date_Range,Code_Range,Code)
 
Last edited:
You must log in or register to reply here.
Back
Top Bottom