Excel won't recognize sheet pattern

Question

I have some rows and the formula is pretty simple:

='01'.B2
='02'.B2
='03'.B2

If I select all three rows and try to autofill, it doesn't follow the sequence of the sheets.

It will go like:

='01'.B2
='02'.B2
='03'.B2
='01'.B4
='02'.B4
='03'.B4

How can I work around this?

what is the meaning of the formula you have? It doesn't seem to be a valid formula. Please note that Excel don't recognize non-valid formulas. — Máté Juhász, Commented Dec 18, 2015 at 20:50
It works. It takes the value of the cell B2 from the sheet 01, 02, 03. — Phiter, Commented Dec 18, 2015 at 21:10

Máté Juhász · Accepted Answer · 2015-12-18 21:14:08Z

0

You can use INDIRECT() as a workaround:

=INDIRECT("'"&TEXT(ROW(A1),"00")&"'.B2")

answered Dec 18, 2015 at 21:14

Máté Juhász

21.6k6 gold badges57 silver badges78 bronze badges

Add a comment |

1 Answer 1