CODEWITHSUNDEEP
excelexcel-formula
Boris Mitioglov
Boris Mitioglov

Reputation: 1182

How to create formula in excel that can iterate in specific range?

I want to fill down 10 cells with value from B10 cell: 

='Disconnect Minor Reason List'!$B$10

Then I want to get another 10 cells with value from B11 cell, then another 10 from B12 and so on. Is it a possible in Excel?

Upvotes: 1

Views: 136

Answers (1)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35853

First approach (better one, since it's not volatile):

  • write formula in E10 cell: =B10
  • write formula in E11 cell: =INDEX($B$10:$B$12,1+INT(COUNTA($E$10:E10)/10))

and drag it down.

Second approach: (if you'd like to use single formula)

use this formula in E10 and drag it down:

=INDEX($B$10:$B$12,1+INT((ROW()-ROW($E$10))/10))

Upvotes: 1

Related Questions