Automate referencing worksheets

A

aaron1000

New Member
Joined
Jun 9, 2011
Messages
10
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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Sort by date Sort by votes
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"))
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
Copy worksheet columns from a second workbook based on the worksheet name in the first workbook - how?
Replies
1
Views
304
kntrigirl1908
K
C
  • Question Question
Complex VBA code - Multiple IF conditions including Worksheet Name contains text partially matching Cell
Replies
1
Views
3K
celebwen_orn
C
J
  • Question Question
Help writing a hyperlink reference to another worksheet based on a number in a neighboring cell
Replies
4
Views
783
offthelip
O
D
  • Question Question
Outlook VBA - Automating the moving of sub-folders via reference to a source/destination table within an Excel workbook
Replies
1
Views
6K
dougmarkham
D
G
  • Question Question
[Help] R1C1 Relative referencing for formulas
Replies
0
Views
328
Gribas
G

Forum statistics

Threads
1,224,525
Messages
6,179,317
Members
452,905
Latest member
deadwings

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top