Autofill sheet references (2003 SP2)

Topic

New Reply

We have a workbook that has a summary sheet and 60 worksheets.
On the summary sheet the same cell reference on each of the sheets listed in a column.
For example =’4′!$B$4, =’5′!$B$4
Is it possible to autofill this down as more sheets are added. I find that using the autofill handle just copies

Your advice would be appreciated.

Reply | Quote

Replies

No, Excel doesn’t do that with sheet references. But you could do the following:
– Enter the numbers 1 and 2 in two cells below each other.
– Fill down as far as needed.
– In the cell next to the 1, enter a formula such as =INDIRECT(“‘”&A2&”‘!$B$4”)
– Fill down as far as needed.

See screenshot.

Reply | Quote

Dear Hans

Fantastic! This has saved a lot of work as nearly 30 more worksheets are being added and it will continue to grow.

Thank you very very much

Regards

Reply | Quote