fill down formula control

[edistogumbo]

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

 

[PaddyD]

what row are you starting in?

paddy

 

[wob]

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.]

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
	A	B	C	D	E	F	G	H	I
1	sheet(1	='sheet(1)'!A$3	='sheet(1)'!A$3
2	sheet(2	='sheet(2)'!A$3	='sheet(2)'!A$3
3	sheet(3	='sheet(3)'!A$3	='sheet(3)'!A$3
4	sheet(4	='sheet(4)'!A$3	='sheet(4)'!A$3
5	sheet(5	='sheet(5)'!A$3	='sheet(5)'!A$3
6	sheet(6	='sheet(6)'!A$3	='sheet(6)'!A$3
7	sheet(7	='sheet(7)'!A$3	='sheet(7)'!A$3
8	sheet(8	='sheet(8)'!A$3	='sheet(8)'!A$3
9
10
Sheet1

 

[PaddyD]

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]

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