How to change which value increments when pasting/filling?

[Christien]

I'm sure this question has been asked and answered before, but obviously I don't know what search terms to use!

When filling a series by dragging the lower right corner handle, how to I force excel to increment a different value?

I want to paste the following formula to many cells:
=IF(X1="y",'[clients.xls]05035'!E100, 0)
(basically, if the column X value indicates the file is active, read the E100 value from worksheet 05035 in clients.xls)

But when I paste it to multiple cells by filling in automatically, I want to increase the file # (in this case, 05035) by one each time, not the reference cell E100 (that should always stay the same). So when I drag it down to fill, or copy paste it, I want to get:
=IF(X1="y",'[clients.xls]05036'!E100, 0)

Any ideas?

Thanks in advance!
Chris

 

[Christien]

anybody?

 

[Datsmart]

The data inside the single quotes in your formula is considered as Text by Excel and therefore will not increment during autofill operations.
I don't know if this is possible. There may be someone that has a clever work around.

 

[Christien]

I'm still pretty new at excel, but I had thought that may the problem, that the stuff in the single quotes isn't considered formula.

Anyone have an idea for a workaround? I don't like the thought of manually entering this in hundreds of cells...

Thanks!