Simple increase a number in a formula in every row

[pic2pic]

Hi!

I have the following formula in a cell:

INDIRECT($K$180&"!"&"Z"&1)

Almost the same formula is in the all column. The only difference is that last number has to change i every consecutive cell, like this:

INDIRECT($K$180&"!"&"Z"&1)
INDIRECT($K$180&"!"&"Z"&2)
INDIRECT($K$180&"!"&"Z"&3)
INDIRECT($K$180&"!"&"Z"&4)
...
...
And so on about 800 times. Off course it would be much easier to just do it like I always do, and that is just press lower right corner of one cell an move it down to the end of desired row and excel will alone increase last number in every cell buy 1. Well, the problem is that excel do not increase the last number, and I don't know why, and how to do that .

Please, can you help me ?

[Pete_UK]

Try it like this:

=INDIRECT($K$180&"!Z"&ROWS($1:1))

If you have spaces in the sheet name in K180, then you will need to include apostrophes (there is no harm in using them anyway), like this:

=INDIRECT("'"&$K$180&"'!Z"&ROWS($1:1))

Hope this helps.

Pete

[Tony Valko]

Maybe like this...

INDIRECT($K$180&"!Z"&ROWS(A$1:A1))

[pic2pic]

Wow, thanks a lot guys It works just fine

[Tony Valko]

You're welcome. We appreciate the feedback!