1

I have the formula =IF(Jan!$L6="w",Jan!$L6,"") in the cell A2.

in the cell right next (B2) to it I'd like to have the formula 3 rows further down so =IF(Jan!$L9="w",Jan!$L9,"") and then in C2 again 3 more down (so =IF(Jan!$L12="w",Jan!$L12,"")) and so on.

I'd like to to that with autofill. I have seen referencing rows to columns directly with help of the index function put not inside of a formula and not with skipping lines.

Improve this question

1 Answer 1

Reset to default
1

If you could live with using an additional row (which you can hide once the formula is in place, or which you even can place on a different worksheet), it is fairly easy to accomplish using the INDIRECT function (very different than the INDEXfunction).

Type L6 in cell A1 and L9 in B1, and in A2 you put the formula
=IF(INDIRECT(CONCATENATE("Jan!";A1))="w";INDIRECT(CONCATENATE("Jan!";A1));"") .
Now you can autofill B2 (copying A1 once to the right), and then you select the four cells (from A1 to B2), and you autofill as far left as you want.

Please note that you have to select both cells (the one containing L6 and the one with L9), so the autofill will recognize the pattern, and will continue with adding 3.

As I assume that you maybe also will have sheets for Feb, Mar, etc., you also can substitute these in the formula, and let the INDIRECT function do its magic.

Improve this answer
2
  • I replaced F1 with A1 and then it worked like a charm, thank you very much. (I suggested a correction to your answer)
    – Nivatius
    Commented Nov 7, 2018 at 21:13
  • Ok thanks, I will edit the answer with your suggestion.
    – Peter K.
    Commented Nov 7, 2018 at 21:16

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .