Automate referencing worksheets

[aaron1000]

Hi Folks, I'm having trouble with how to program a maco that will automate the reference of sequential worksheet names in serveral forumlas in a 'calcs' worksheet

The 'calcs' worksheet contains six columns (A to F)... each with a different formula. Each row in the column references a worksheet of which there are 250 so far.

For example:

Calcs Worksheet
A1 -> =MIN('1'!C1,'1'!C6000)
A2 -> =MIN('2'!C1,'2'!C6000)
A3 -> =MIN('3'!C1,'3'!C6000)
A4 -> =MIN('4'!C1,'4'!C6000)
.
.
.
and I want to automate the referencing all the way upto (so far) worksheet 250
.
.
A250 -> =MIN('250'!C1,'250'!C6000)


Appreciate an help on this.

cheers
Aaron

 

[Derek Brown]

You could try using the INDIRECT function but, be warned, this is a volatile function that will recalculate whenever calculation occurs so having many such instances of the function will slow calculation of the workbook.
=MIN(INDIRECT("'"&ROW()&"'!C1"),INDIRECT("'"&ROW()&"'!C6000"))

 

[aaron1000]

Thanks, it appears to be working a treat.

cheers
Aaron