OFFSET a non-contiguous named range in a formula

[AyurvedaPura]

Hi

I have as the two logical operators for an IF function the following COUNTA calculation which checks how many blanks are in the range GrdMtx7 and subtracts this from the value in E5 and adds the suffix "Module(s)".

(E5-COUNTA(GrdMtx7)-1)&" Module(s)",(E5-COUNTA(GrdMtx7)&" Module(s)")))

GrdMtx7 is a non-contiguous range consisting of the following cell references on a second sheet:

='Mod Schedule'!$E$7,'Mod Schedule'!$H$7,'Mod Schedule'!$K$7,'Mod Schedule'!$N$7,'Mod Schedule'!$Q$7,'Mod Schedule'!$T$7,'Mod Schedule'!$W$7,'Mod Schedule'!$Z$7,'Mod Schedule'!$AC$7,'Mod Schedule'!$AC$7,'Mod Schedule'!$AF$7,'Mod Schedule'!$AI$7,'Mod Schedule'!$AL$7,'Mod Schedule'!$AO$7,'Mod Schedule'!$AR$7,'Mod Schedule'!$AU$7,'Mod Schedule'!$AX$7

I need for the formula of the IF function to copy down so that it refers to F5, G5, H5, I5...etc.

I also need for the non-contiguous named range GrdMtx7 to refer to the same non-contiguous range shifted down one row intact for the COUNTA function to operate on the next row down, such that COUNTA is checking for blanks in the range:

='Mod Schedule'!$E$8,'Mod Schedule'!$H$8,'Mod Schedule'!$K$8,'Mod Schedule'!$N$8,'Mod Schedule'!$Q$8,'Mod Schedule'!$T$8,'Mod Schedule'!$W$8,'Mod Schedule'!$Z$8,'Mod Schedule'!$AC$8,'Mod Schedule'!$AC$8,'Mod Schedule'!$AF$8,'Mod Schedule'!$AI$8,'Mod Schedule'!$AL$8,'Mod Schedule'!$AO$8,'Mod Schedule'!$AR$8,'Mod Schedule'!$AU$8,'Mod Schedule'!$AX$8

I can't get got to work at all using OFFSET and would rather not create dozens or hundreds of named ranges incrementing one at a time - is there a way to achieve this, preferably with a formula, but vba if absolutely necessary.

My best effort doesn't work:

(E6-COUNTA(OFFSET(GrdMtx7,1,))-1)&" Module(s)",(E6-COUNTA(OFFSET(GrdMtx7,1,)))&" Module(s)")

Any help hugely appreciated, very tight deadline!!

Thanks In Advance

Daniel

 

[p45cal]

As you've discovered, worksheet formulae don't like OFFSETting non-contiguous ranges.
One solution is to have a user defined function (because VBA doesn't mind offsetting such ranges). This one will offset any range, contiguous or not by the supplied (or not) numbers of rows and columns:

Function myOffset(SourceRng As Range, Optional row As Long = 0, Optional column As Long = 0) As Range
Set myOffset = SourceRng.offset(row, column)
End Function

You might br lucky; try prefixing every instance of OFFSET in your examples with my.
But I would suggest changing your named range to be on row 1 of the sheet because then determining the number of rows to offset is easier.
The COUNTA(GrdMtx7) part of your formula could be:
=COUNTA(myoffset(GrdMtx7,6))
and that 6 could be replaced by the likes of Row(A6) or Row()-1 if you want it to refer to the same row as the formula's on, both of which would allow you to copy down or autofill.

 

[AyurvedaPura]

As you've discovered, worksheet formulae don't like OFFSETting non-contiguous ranges.
One solution is to have a user defined function (because VBA doesn't mind offsetting such ranges). This one will offset any range, contiguous or not by the supplied (or not) numbers of rows and columns:

Function myOffset(SourceRng As Range, Optional row As Long = 0, Optional column As Long = 0) As Range
Set myOffset = SourceRng.offset(row, column)
End Function

You might br lucky; try prefixing every instance of OFFSET in your examples with my.
But I would suggest changing your named range to be on row 1 of the sheet because then determining the number of rows to offset is easier.
The COUNTA(GrdMtx7) part of your formula could be:
=COUNTA(myoffset(GrdMtx7,6))
and that 6 could be replaced by the likes of Row(A6) or Row()-1 if you want it to refer to the same row as the formula's on, both of which would allow you to copy down or autofill.

Thanks for this, I will give it a whirl

Daniel