Closed Thread
Results 1 to 7 of 7

how do i copy vertical formulas to horizonal and keep same cell r.

  1. #1
    opiedrake
    Guest

    how do i copy vertical formulas to horizonal and keep same cell r.

    when i copy formulas in a row, and transpose the copy, it changes the cell
    references to other cells which needs to be the same as the source cells

  2. #2
    Earl Kiosterud
    Guest

    Re: how do i copy vertical formulas to horizonal and keep same cell r.

    Opie,

    The relative cell reference adjustment is useful when the referred cell is
    in the table you're transposing. Unfortunately, the transpose command
    adjusts it when the referred cell is external to the table, where you most
    likely want the reference to remain to the same cell. You can get around
    this with absolute references:

    =......$A$3...... The F4 key is handy for changing to absolute. Do
    this while you're editing the cell.

    It will not change absolute references to referred cells outside the table,
    but will change them for cells internal to the table. Probably the best for
    most situations. If the cells are internal (unlikely, I think), probably
    the only way is to use:

    =.......INDIRECT("A3")......
    --
    Earl Kiosterud
    mvpearl omitthisword at verizon period net
    -------------------------------------------

    "opiedrake" <opiedrake@discussions.microsoft.com> wrote in message
    news:10E4383E-3FBA-476C-A2A4-5B8753492708@microsoft.com...
    > when i copy formulas in a row, and transpose the copy, it changes the cell
    > references to other cells which needs to be the same as the source cells




  3. #3
    Dave O
    Guest

    Re: how do i copy vertical formulas to horizonal and keep same cell r.

    1. Highlight the vertical region that contains your formulas.
    2. Do a search and replace: search for the equal sign, and replace it
    with your initials. This converts all your formulas to text.
    3. Copy the vertical region, and >Paste special >Transpoze. This
    converts the vertical region to horizontal.
    4. Search and replace again, but this time search for your initials
    and replace with the equal sign.

    When you converted the formulas to text the formulas became static. By
    replacing your initials with the = sign in step 4 you converted them
    back to dynamic formulas.


  4. #4
    Dave O
    Guest

    Re: how do i copy vertical formulas to horizonal and keep same cell r.

    1. Highlight the vertical region that contains your formulas.
    2. Do a search and replace: search for the equal sign, and replace it
    with your initials. This converts all your formulas to text.
    3. Copy the vertical region, and >Paste special >Transpoze. This
    converts the vertical region to horizontal.
    4. Search and replace again, but this time search for your initials
    and replace with the equal sign.

    When you converted the formulas to text the formulas became static. By
    replacing your initials with the = sign in step 4 you converted them
    back to dynamic formulas.


  5. #5
    Registered User
    Join Date
    02-06-2012
    Location
    Durban, South Africa
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: how do i copy vertical formulas to horizonal and keep same cell r.

    Quote Originally Posted by Dave O View Post
    1. Highlight the vertical region that contains your formulas.
    2. Do a search and replace: search for the equal sign, and replace it
    with your initials. This converts all your formulas to text.
    3. Copy the vertical region, and >Paste special >Transpoze. This
    converts the vertical region to horizontal.
    4. Search and replace again, but this time search for your initials
    and replace with the equal sign.

    When you converted the formulas to text the formulas became static. By
    replacing your initials with the = sign in step 4 you converted them
    back to dynamic formulas.
    blooming marvelous!!!

  6. #6
    Registered User
    Join Date
    07-22-2014
    Location
    Dublin
    MS-Off Ver
    2010
    Posts
    17

    Re: how do i copy vertical formulas to horizonal and keep same cell r.

    Quote Originally Posted by Woodstock View Post
    blooming marvelous!!!
    To go one step further with the above, how would you do this between different sheets? the find replace tip is good but only works within one sheet..

  7. #7
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: how do i copy vertical formulas to horizonal and keep same cell r.

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

Closed 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