+ Reply to Thread
Results 1 to 3 of 3

Copy one cell fomula to another

  1. #1
    Registered User
    Join Date
    02-13-2009
    Location
    sg
    MS-Off Ver
    Excel 2007
    Posts
    36

    Copy one cell fomula to another

    I would like to copy one cell formula to another but i would like the constant to remain intact. When i pull it over to another cell there is an incremental effect.

    from:
    =SUM(LEN(B9:B300)-LEN(SUBSTITUTE(B9:B300,B4&","&D8,"")))/(LEN(B4&","&D8))

    to
    =SUM(LEN(B9:B300)-LEN(SUBSTITUTE(B9:B300,B5&","&D8,"")))/(LEN(B5&","&D8))

    But i want it to be either

    =SUM(LEN(B9:B300)-LEN(SUBSTITUTE(B9:B300,B4&","&D8,"")))/(LEN(B4&","&D8))

    or better still simple increment just value
    =SUM(LEN(B9:B300)-LEN(SUBSTITUTE(B9:B300,B4&","&D9,"")))/(LEN(B4&","&D9))

    thanks
    Last edited by superkid; 02-15-2009 at 03:37 AM.

  2. #2
    Valued Forum Contributor mudraker's Avatar
    Join Date
    11-10-2003
    Location
    Melbourne, Australia
    Posts
    3,983

    Re: Copy one cell fomula to another

    Assuming you want D8 to remain D8 change D8 to D$8

    The $ in front of the 8 means the 8 will not change
    You can also place a $ infront of the D do the colunm D does not change

    examples
    $D8 - Column will not change
    D$8 - Row will not change
    $D$8 - Row & Column will not change

    Your formula will be something like this - depending on what rows & columns are not to be changed
    =SUM(LEN($B$9:$B$300)-LEN(SUBSTITUTE($B$9:$B$300,B4&","&D9,"")))/(LEN(B4&","&D9))

    See help file on
    Switch between relative, absolute, and mixed references
    Last edited by mudraker; 02-15-2009 at 02:34 AM.
    Please Read Forum Rules Before Posting
    Wrap VBA code by selecting the code and clicking the # icon or Read This
    How To Cross Post politely

    Top Excel links for beginners to Experts

    If you are pleased with a member's answer then use the Scales icon to rate it
    If my reply has assisted or failed to assist you I welcome your Feedback.

  3. #3
    Registered User
    Join Date
    02-13-2009
    Location
    sg
    MS-Off Ver
    Excel 2007
    Posts
    36

    Re: Copy one cell fomula to another

    thank you :D got it running . you guys have been of a great help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1