Results 1 to 6 of 6

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. #1
    Board Regular
    Join Date
    Feb 2009
    Posts
    103

    Default 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. #2
    Board Regular
    Join Date
    Aug 2005
    Posts
    4,795

    Default Re: Formula, fill down a column and fill across rows

    your problem is not clear

    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
    please clarify
    I am not an expert. So better solutions may be available
    MinE WINDOWS 7 AND excel 2007(compatbililty mode)
    venkat1926(at)gmail(dot)com
    preferably do not send private messages in the newsgroup reply to newsgroup

  3. #3
    Board Regular
    Join Date
    Feb 2009
    Posts
    103

    Question 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. #4
    Board Regular
    Join Date
    Aug 2005
    Posts
    4,795

    Default 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
    =

    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
    =

    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.
    I am not an expert. So better solutions may be available
    MinE WINDOWS 7 AND excel 2007(compatbililty mode)
    venkat1926(at)gmail(dot)com
    preferably do not send private messages in the newsgroup reply to newsgroup

  5. #5
    Board Regular
    Join Date
    Aug 2005
    Posts
    4,795

    Default 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
    =

    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
    =

    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.
    I am not an expert. So better solutions may be available
    MinE WINDOWS 7 AND excel 2007(compatbililty mode)
    venkat1926(at)gmail(dot)com
    preferably do not send private messages in the newsgroup reply to newsgroup

  6. #6
    Board Regular
    Join Date
    Feb 2009
    Posts
    103

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

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com