CODEWITHSUNDEEP
excel
Dmitrij Holkin
Dmitrij Holkin

Reputation: 2055

Excel offset formula each 10 row

I have formula in data validation list

=OFFSET(D1,1,0,42,1)

Which display all 42 cells values in range("D2:D42")

But how to show only each 10th row to get list of 4th values D2;D12;D22;D42?

Upvotes: 0

Views: 99

Answers (1)

Tom Sharpe
Tom Sharpe

Reputation: 34265

You are hoping for a magic way of doing this, but I don't know of one. Excel only allows a continuous range for a validation list.

I suggest making life easy for yourself and just having a new list in (say) E2:E6 containing 

=D2
=D12

and so on.

If you wanted it to be a bit more dynamic you could use

=INDEX(D:D,ROWS(D$1:D1)*10-8)

starting in (say) F2 and pull it down.

Then define a named range VList

=OFFSET(Sheet1!$F$2,0,0,INT(COUNTA(Sheet1!$D:$D)/10)+1,1)

assuming there are no empty cells in between the values in column D and use this in your validation rule, so that if another ten cells were added in column D down to D52, the new value would be included in the validation list.

If there were empty cells between D2, D12 etc. and D2, D12 etc. contained text then the formula for VList would be

=OFFSET(Sheet1!$F$2,0,0,INT(MATCH(REPT("z",255),Sheet1!$D:$D)/10)+1,1)

Upvotes: 1

Related Questions