Drag and autofill

Andrew XJ · Feb 26th, 2002, 06:21 AM

I don't know whether it is a silly question but i really can't solve it.
in Sheet1:
in Cell A1 I input formula ='Sheet2'!$A1
in Cell A2, ='Sheet2'!$A4
I want to use Select and drag to autofill the subsequent. But fail. I have also added a series 1,4,7 to the series. I have used Macro to solve this problem, but how to use select and drag to auto fill the formula?

Juan Pablo González · Feb 26th, 2002, 06:54 AM

Try with:

=INDIRECT("Sheet2!A" & (3*ROW(A1)-2))

and drag down.

Mark W. · Feb 26th, 2002, 07:59 AM

Here's another way... that will leave the simplest formula in these cells.

1. Enter "Sheet2!$A1" (without the quotes) in cell Sheet1!A1 (or wherever).
2. Enter "Sheet2!$A4" in cell Sheet1!A2.
3. Select cells A1:A2 and drag the fill handle down the column as far as needed.
4. Select column A:A and choose the Edit | Replace... menu command replacing all instances of "Sheet2" with "=Sheet2".

[ This Message was edited by: Mark W. on 2002-02-26 06:59 ]

[ This Message was edited by: Mark W. on 2002-02-26 07:15 ]

Andrew XJ · Feb 26th, 2002, 08:40 AM

Thanks!!! Juan and Mark. Both of your methods works fine!!

Q · Jun 6th, 2002, 09:41 AM

q

Feb 26th, 2002, 06:54 AM	#2
Juan Pablo González MrExcel MVP Join Date: Feb 2002 Location: Bogota, Colombia Posts: 11,927	Try with: =INDIRECT("Sheet2!A" & (3*ROW(A1)-2)) and drag down. __________________ Regards, Juan Pablo González http://www.juanpg.com

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Feb 26th, 2002, 06:21 AM	#1
Andrew XJ Board Regular Join Date: Feb 2002 Location: Singapore Posts: 77	I don't know whether it is a silly question but i really can't solve it. in Sheet1: in Cell A1 I input formula ='Sheet2'!$A1 in Cell A2, ='Sheet2'!$A4 I want to use Select and drag to autofill the subsequent. But fail. I have also added a series 1,4,7 to the series. I have used Macro to solve this problem, but how to use select and drag to auto fill the formula?

Feb 26th, 2002, 07:59 AM	#3
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	Here's another way... that will leave the simplest formula in these cells. 1. Enter "Sheet2!$A1" (without the quotes) in cell Sheet1!A1 (or wherever). 2. Enter "Sheet2!$A4" in cell Sheet1!A2. 3. Select cells A1:A2 and drag the fill handle down the column as far as needed. 4. Select column A:A and choose the Edit \| Replace... menu command replacing all instances of "Sheet2" with "=Sheet2". [ This Message was edited by: Mark W. on 2002-02-26 06:59 ] [ This Message was edited by: Mark W. on 2002-02-26 07:15 ]

Feb 26th, 2002, 08:40 AM	#4
Andrew XJ Board Regular Join Date: Feb 2002 Location: Singapore Posts: 77	Thanks!!! Juan and Mark. Both of your methods works fine!!

Jun 6th, 2002, 09:41 AM	#5
Q Board Regular Join Date: Apr 2002 Location: 20 Minutes outside Manhattan Posts: 64	q