Moving array formulas

A

Anthony Slater

I have the following formular that is working. However, it needs to be the
same for 6 rows but then moving the references accordingly. B needs to be
increased by 6 every 6 rows. H & F need to be increased by 1 every 6 rows
(see below for how it needs to be)

ie
Rows A1 to A6
{=if($B$2=H3,F3)}

Rows A7 to A12
{=if($B$8=H4,F4)}

Rows A13 to A18
{=if($B$14=H5,F5)}


How can I acheive this?
 
M

Max

One way ..

Put in A1:

=IF(INDIRECT("B"&(INT((ROW(A1)+5)/6)-1)*6+2)=INDIRECT("H"&INT((ROW(A1)+5)/6)
+2),INDIRECT("F"&INT((ROW(A1)+5)/6)+2),"")

(just press ENTER, it's not an array formula)

Copy A1 down as desired,
but to terminate at a multiple of 6 rows
e.g. at: A6, A12, A18, A24, A30, etc

The above should return the results that you're after ..

Note that the formula will return blanks: ""
as the value_if_false,
instead of the value: FALSE
(thought "blanks" was a cleaner output to have)

You could also suppress* extraneous zeros from showing via:
Tools > Options > View tab > Uncheck "Zero values" > OK

*if you're copying down ahead of data input in cols B, H & F
 
Top