fill down formula control

edistogumbo

New Member
Joined
Nov 4, 2002
Messages
1
If you fill down a formula such as
=('sheet(2)'!A$3) you get this same formula in each cell you filled. How can you get the following results:
=('sheet(2)'!A$3)
=('sheet(3)'!A$3)
=('sheet(4)'!A$3)
=('sheet(5)'!A$3)
etc.

I need the sheet # in the formula to grow linearly while keeping the absolute reference cell on each sheet the same
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

wob

Board Regular
Joined
May 21, 2002
Messages
105
this assumes that column B has the sheet numbers that you need

=CONCATENATE("=sheet",B1,"!A$",B1)

you could drag fill this down a column, then right click paste special values this into where you need it?
 

Mark W.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
Enter "sheet(1" into cell A1 and drag down as needed. In column B enter formulas shown below. Copy formulas in column B and Paste Special... Values over the formulas in column B or in column C as shown below. Finally, with column C selected replace = with =.
Book1
ABCDEFGHI
1sheet(1='sheet(1)'!A$3='sheet(1)'!A$3
2sheet(2='sheet(2)'!A$3='sheet(2)'!A$3
3sheet(3='sheet(3)'!A$3='sheet(3)'!A$3
4sheet(4='sheet(4)'!A$3='sheet(4)'!A$3
5sheet(5='sheet(5)'!A$3='sheet(5)'!A$3
6sheet(6='sheet(6)'!A$3='sheet(6)'!A$3
7sheet(7='sheet(7)'!A$3='sheet(7)'!A$3
8sheet(8='sheet(8)'!A$3='sheet(8)'!A$3
9
10
Sheet1
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
do it in one go with a formula of the form:

=INDIRECT("sheet"&ROW()&"!$A$1")

add / subtract something from the row() portion of the formula to reflect the row you're starting from. E.g. if you're starting in row 10, and want to start from sheet2:

=INDIRECT("sheet"&ROW()-8&"!$A$1")

paddy
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
and if you want just the labels and not the values use Paddy's formula without the INDIRECT ... to read

="sheet"&ROW()-2&"!$A$3" ... if the formula was entered in row 3

Regards!
Yogi Anand
This message was edited by Yogi Anand on 2002-11-05 18:56
 

Forum statistics

Threads
1,144,328
Messages
5,723,730
Members
422,512
Latest member
MHau5

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
Top