Advice: Dynamic named range Discontinuous, Non-VBA

[Zack Barresse]

There is a workaround though. If, as in your last example Dave, you have a list of information in column A in which you want to extract every nth row of information to a populated list or dynamically named range. This can be done with the addition of a second column. At least that's the only way I know how to do it.

We'll say your looking at column A, full of information. You only want every 10th row of information starting with row 1 (as in your last post: 1, 11, 21, 31, etc.). In column B you could put something like this...

=INDEX($A:$A,(CELL("row",$A1)-CELL("row",$A$1))*$C$1+1)

..and copy down. Then use a dynamic range, as your formula above looks (or I prefer an INDEX/MATCH combination instead of the OFFSET), or:

=Sheet1!$B$1:INDEX(Sheet1!$B:$B,MATCH(REPT("z",255),Sheet1!$B:$B))

This is of course if you have text in column A. If your formula goes past the nth row it will show 0. Which you could always use custom formatting on, something like this:

[=0]"";@;General

Will give you the same results w/o using OFFSET. Change range/sheet name to suit. But it works.

Maybe you could use this, hope it helps.

Example is uploaded.

EDIT: BTW, cell D1 in the sample file has the DV drop-down list from column B.