CODEWITHSUNDEEP
excelexcel-formula
Tickets2Moontown
Tickets2Moontown

Reputation: 137

Reference cells with no gaps to fill table with gaps

I have included 2 tables below to illustrate my problem.

Table 1

enter image description here

Table 2

enter image description here

I am trying to find a formula that fills rows 140, 143 & 146 (Table 2) from rows 15,16 & 17 (Table 1). There is over 100 so it is quite time consuming to input =B15 etc over and over again.

The offset method e.g. =OFFSET($B$15,(ROW()-1)*3,0) only works when I'm referencing gaps, not trying to fill them.

Essentially, where B140's formula is =B15, B143's will be =B140 + 1 row i.e. B16

Thanks for your help!

Upvotes: 0

Views: 183

Answers (2)

basic
basic

Reputation: 11968

If you are trying find value for appropriate month you can use INDEX/MATCH entered as array formula:

=IFERROR(INDEX($B$1:$B$4,MATCH(TRUE,MONTH(A10)=MONTH($A$1:$A$4),0)),"")

Array formula after editing is confirmed by pressing ctrl + shift + enter

enter image description here

Edit

To find by month & year use:

=IFERROR(INDEX($B$1:$B$4,MATCH(1,(MONTH(A10)=MONTH($A$1:$A$4))*(YEAR(A10)=YEAR($A$1:$A$4)),0)),"")

it's also array formula

Upvotes: 1

T. Altena
T. Altena

Reputation: 811

You can use modulo for this. With the Modulo function, you check if the remainder of the row you're on is divisible by a number (e.g. 3 if you want to copy a value every third row). IF(MOD(ROW(E1);3 = 0)

If that's the case, you can divide by 3 and use for example the Index function to copy the nth value of another location (or another worksheet). If that's not the case, you print "" to get an empty row.

=IF(MOD(ROW(E1);3)=0;INDEX($B$1:$B$4;ROW(E1)/3);"")

enter image description here

If you're working with offsets because the row numbers are not on numbers divisible by three, you could manually offset the rows (and do the same for the division that yields the index row). For example, if you want to have rows 2, 5, 8 etc:

=IF(MOD(ROW(E1)+1;3)=0;INDEX($B$1:$B$4;ROW(E2)+1/3);"")

Upvotes: 0

Related Questions