# Formula, fill down a column and fill across rows

This is a discussion on Formula, fill down a column and fill across rows within the Excel Questions forums, part of the Question Forums category; I've tried a few different formulas with no luck. If I do it the long way, going back and forth ...

1. ## Formula, fill down a column and fill across rows

I've tried a few different formulas with no luck.
If I do it the long way, going back and forth sheets the formulas are...
=SUM(12500/Data!C18)*(Data!C28-Data!C18)
=SUM(12500/Data!C27)*(Data!C37-Data!C27)
I need to change the first formula so that I can copy/paste special/formulas down the column and across the rows of a sheet.

2. ## Re: Formula, fill down a column and fill across rows

copy/paste special/formulas down the column and across the rows of a sheet.
suppose A1 of another sheet has this formula
=SUM(12500/Data!C18)*(Data!C28-Data!C18)
then what do you want in A2
is it
=SUM(12500/data!C19)*(data!C29-data!C19)
and what do you want in B1
is it
=sum(a2500/data!d18)*(data!d28-data!d18)
and so on both down and right

3. ## Re: Formula, fill down a column and fill across rows

Sorry, I should have explained in detail more.
Sheet 1 (Data!) is a huge table of information. Sheet 2, I want certain data out of sheet 1 (every 9th row, C18/C27 and 28/37). Hoping that it's possible to only have one formula where by I can copy/paste special/formula down and across the columns for sheet 2.
Column C
row 18 15.00 =SUM(12500/Data!C18)*(Data!C28-Data!C18) - A4 sheet 2
row 19 15.48
row 20 0.48
row 21 -0.19
row 22 2,923,797
row 23 15.18
row 24 15.40
row 25 15.07
row 26 15.08
row 27 14.88 =SUM(12500/Data!C27)*(Data!C37-Data!C27) - A5 sheet 2
row 28 15.41
row 29 0.53
row 30 -0.12
row 31 3,134,140
row 32 15.07
row 33 15.30
row 34 15.40
row 35 15.40
row 36 15.10=SUM(12500/Data!C36)*(Data!C46-Data!C36) - A6 sheet 2 and so on...
row 37 15.41

4. ## Re: Formula, fill down a column and fill across rows

your clarifications are not complete. where are the 15.00,15.48 , 0.48 etc -are they in column B of sheet "data" or in column C of data.
it is not clear what is meant by copying to the right. you should have given simiar formulas for B4,B5 B6as you have given for A4,A5,A6.
you use - when writing -sheet2 A4. this - can be confused for minus sign.

I have added some fictitious numbers in column C of sheet1 and appended it below.
I have also appended sheet 2 with formula in A4 white is copied down . the expression SUM is not required in your formula
modify carefully formulas to suit you
******** language="JavaScript" ************************************************************************>
 Microsoft Excel - hotrod 1.xls ___Running: xl2002 XP : OS = Windows XP
 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
 E35E36E37 =

A
B
C
D
E
F
18
row 18151
19
row 1915.482
20
row 200.483
21
row 21-0.194
22
row 222,923,7975
23
row 2315.186
24
row 2415.47
25
row 2515.078
26
row 2615.089
27
row 2714.8810
28
row 2815.4111
29
row 290.5312
30
row 30-0.1213 =SUM(12500/Data!C18)*(Data!C28-Data!C18)
31
row 313,134,14014 =SUM(12500/Data!C27)*(Data!C37-Data!C27)
32
row 3215.0715 =SUM(12500/Data!C36)*(Data!C46-Data!C36)
33
row 3315.316
34
row 3415.417 check values
35
row 3515.418 6944.444in sheet2 A4
36
row 3615.119 4629.63in sheet2 A5
37
row 3715.4120 3472.222in sheet2 A6
 data

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

******** language="JavaScript" ************************************************************************>
 Microsoft Excel - hotrod 1.xls ___Running: xl2002 XP : OS = Windows XP
 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
 A4A5A6 =

A
B
C
D
1
2
3
4
6944.444444
5
4629.62963
6
3472.222222
7
 Sheet2

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

5. ## Re: Formula, fill down a column and fill across rows

the sheets given by me are wrong. see the correct sheets below

******** ******************** ************************************************************************>
 Microsoft Excel - hotrod 1.xls ___Running: xl2002 XP : OS = Windows XP
 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
 E35E36E37 =

A
B
C
D
E
F
G
H
18
row 18151
19
row 1915.482
20
row 200.483
21
row 21-0.194
22
row 222,923,7975
23
row 2315.186
24
row 2415.47
25
row 2515.078
26
row 2615.089
27
row 2714.8810
28
row 2815.4111
29
row 290.5312
30
row 30-0.1213 =SUM(12500/Data!C18)*(Data!C28-Data!C18)
31
row 313,134,14014 =SUM(12500/Data!C27)*(Data!C37-Data!C27)
32
row 3215.0715 =SUM(12500/Data!C36)*(Data!C46-Data!C36)
33
row 3315.316
34
row 3415.417 check values
35
row 3515.418 125000in sheet2 A4
36
row 3615.119 12500in sheet2 A5
37
row 3715.4120 6578.947in sheet2 A6
38
21
39
22
40
23
41
24
42
25
43
26
44
27
45
28
 data

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

******** ******************** ************************************************************************>
 Microsoft Excel - hotrod 1.xls ___Running: xl2002 XP : OS = Windows XP
 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
 A4A5A6 =

A
B
C
D
4
125000
5
12500
6
6578.947368
 Sheet2

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

6. ## Re: Formula, fill down a column and fill across rows

Venkat, I can't thank you enough.
It would have taken me more than a month of Sundays to work it out.