Need help with Dynamic Named ranges in middle of worksheet

[StephenMcGill]

hi I need some help please. I have a worksheet with different sections of data that I need to interrogate with Index, Small, Row formulas to create separate summary reports. Ideally the main data worksheet would be split up so each section is its own worksheet which would mean I could easily set up Dynamic named ranges for each to use in formulas, but alas that is not possible for how the business wants to use this sheet.

So I need a way to create dynamic named range for a section in the middle of a worksheet. I's columns B to T and currently it starts row #13 to #93 but as more data is entered or deleted that would change and that is what I cant get my head around - all dynamic range tutorials I've found assume one dataset on the worksheet so can use counting until blank rows are found. I cant do that since here;'s another, different, dataset currently starting row#94 (that would change)

hope someone can help as i'm well and truly stuck.

many thanks
Stephen

 

[jasonb75]

Do you have row labels in column A?

If you can find something that is always consistent to the start and end of each table then you could use exact match to identify the positions.

 

[StephenMcGill]

Col A is blank.

however the start of the next section has a title so I have just realised I could add name range (_startof2ndsection) and the do 'ROW(_startof2ndsection)-1' to get the last row of the data section I need

so then somehow I need to get that into the monster formula to replace $Q$93 that I have at the moment

=IF(ISERROR(INDEX('Production Meet. Mins '!$B$13:$Q$93,SMALL(IF('Production Meet. Mins '!$Q$13:$Q$93="",ROW('Production Meet. Mins '!$Q$13:$Q$93)),ROW(1:1))-12,3)),"",INDEX('Production Meet. Mins '!$B$13:$Q$93,SMALL(IF('Production Meet. Mins '!$Q$13:$Q$93="",ROW('Production Meet. Mins '!$Q$13:$Q$93)),ROW(1:1))-12,3))

 

[mikerickson]

If column B determines last row and column B has text entries, try this definition

=Sheet1!$B$3:INDEX(Sheet1!$T:$T, MATCH("zzzz", Sheet1!$B:$B), 1)

If column B has numeric entries, replace "zzz" with 1E+300

 

[StephenMcGill]

I think i'm getting there. But how do I refer to column $Q with 'ROW(_rngstarof2ndsection)-1' in this formula pls (probably a silly q) -thanks

=IF(ISERROR(INDEX('Production Meet. Mins '!$B$13:$Q$93,SMALL(IF('Production Meet. Mins '!$Q$13:$Q$93="",ROW('Production Meet. Mins '!$Q$13:$Q$93)),ROW(1:1))-12,3)),"",INDEX('Production Meet. Mins '!$B$13:$Q$93,SMALL(IF('Production Meet. Mins '!$Q$13:$Q$93="",ROW('Production Meet. Mins '!$Q$13:$Q$93)),ROW(1:1))-12,3))

 

[mikerickson]

If you use that definition to define NamedRange, column Q is the 16th column of NamedRange, so

=INDEX(NamedRange, 0, 16) will refer to those cells.

 

[StephenMcGill]

im being thick - I don't understand.#
I have named the first row of the next section, so with ROW i now know where section 1 finishes but don't know how to use that in my formula.