Autofill worksheet numbers

[Lewy2]

I have spread sheet I am designing that has columns with the following formula in them

=Sheet(2)$c$20
=Sheet(3)$c$20

This can run through to 100 sheets is there a way I can Autofill the Sheet numbers.

[Mark W.]

On 2002-04-07 16:21, Lewy2 wrote:
I have spread sheet I am designing that has columns with the following formula in them

=Sheet(2)$c$20
=Sheet(3)$c$20

This can run through to 100 sheets is there a way I can Autofill the Sheet numbers.

I assume you mean...

=Sheet2!$C$20
=Sheet3!$C$20

If so, you could enter "Sheet2" into a cell (e.g., A1) and fill down by dragging the Fill handle. In an adjacent column (e.g., B1) enter the formula, ="="&A1&"!$C$20".

Now, you can select, Copy and overlay the formulas in column B with their resulting values by using Paste Special... Values. Finally, with column B still selected use Edit | Replace... to replace = with =.

[ This Message was edited by: Mark W. on 2002-04-07 16:45 ]