I have a set of multiple rows (1 to 10) and columns (A to G) which is has the named range 'AllData'
In one column I have a formula that gets the address of the range for all cells in column E for all rows BEFORE each row. The formula is below
=Address(Row(AllData),Column(AllData)) & ":" & Address(Row()-1,Column(AllDAta))
I then use 'Indirect' to convert this address into a range object for use in my functions e.g. "Indirect(E6)" will return the range reference 'E1:E5'
Whilst this is fine for most formulas one needs to return same range of rows by from column 'B' - to give 'B1:B5'
I could of course use another 'helper' column to give me the 'B rows until now' range but I don't want to do that (for various reasons).
In VBA I would use the 'EntireRow' property of the range object for 'E1:F5' to give a range object of '1:5' and then I could use this to, via an intersection, to create a new range object 'B1:B5'
How can I achieve the same 'EntireRow' functionality in a worksheet function ?
Now of course I could use 'Offset' but I want a formula that is dynamic and does not have any hard-coded values (like the column offset number would be) and that is why I used named ranges. Named ranges guarantee an absolute range and can be easily extended as needed.