+ Reply to Thread
Results 1 to 8 of 8

Copy cell formulas in 2 directions (vertical and horizontal )

  1. #1
    Registered User
    Join Date
    10-17-2015
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    3

    Angry Copy cell formulas in 2 directions (vertical and horizontal )

    Hi I'm trying to copy a cell in 2 directions but the formula has links that don't follow

    here's an example :

    A1(value)
    A2(Value) C2(= $A$1*A2)


    If I drag A2 and C2 down it works they all relate to A1 because of the $ and to the cell beside because of the lack of $.

    When A2 and C2 are dragged and filled with multiple data I want to copy all of it to the right (A1, A2 and C2 become H1, H2 and J2 etc) but when I do that I need to fix the formula to every beginning of the selection and redrag everything down....

    Is there a way to automate that? It's something that should exist and and tired of searching everywhere.....

    Thanks
    Bruno

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    20,727

    Re: Copy cell formulas in 2 directions (vertical and horizontal )

    What should D2 look like after you make the repairs.

  3. #3
    Registered User
    Join Date
    10-17-2015
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    3

    Re: Copy cell formulas in 2 directions (vertical and horizontal )

    it would look like this :
    A1(value)
    A2(Value) C2(= $A$1*A2)
    A3(Value) C3(= $A$1*A3)
    ... (column A)

    D1(value)
    D2(Value) F2(= $D$1*D2)
    D3(Value) F3(= $D$1*D3)
    ... (column B)


    I would like to copy column A to become column B etc I have like 80 and so column to do (that's why I want to make it automatic). once that is done i'd also like to copy the big section of rows into multiple rows....

    Hope this helps

  4. #4
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL (COVID allowing)
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,403

    Re: Copy cell formulas in 2 directions (vertical and horizontal )

    Instead of =$A$1*A2, try using this:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    If you then copy A1:C3 to D1:F3, the formula in C2 will automatically give you D1*D2.

    Hope that helps.
    Regards,
    Aardigspook

    Recently moved house, internationally, during COVID (!) so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.

  5. #5
    Registered User
    Join Date
    10-17-2015
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    3

    Re: Copy cell formulas in 2 directions (vertical and horizontal )

    Don't seem to be able to apply the offset function...

    So I think it'll be easier to give my current formulas and working throught that it'll be easier..


    A1= value
    A2= 0.9*A1
    A3 = value
    A4 = value (...) b4 = $A$3*$A$2*A4/$A$15 (...) c4=b4+(1-$A$3)*$A$2/$A$16
    ...
    A15=SUM(a4:a14)
    A16= value


    I want to be able to drag down from 4 to 14 to create my basic table. Once the basic table done I want to copy the table to the right 40ish times.... Once the 40 copies are done I need to copy that big set of rows down to create another set of row..... I hope this is clear :S

    Now I need to retype (adapt) the formulas and redrag from row 4to 14

    is it possible?

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,391

    Re: Copy cell formulas in 2 directions (vertical and horizontal )

    I think it would be helpful if you could upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  7. #7
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL (COVID allowing)
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,403

    Re: Copy cell formulas in 2 directions (vertical and horizontal )

    Try this:
    In B4:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    In C4:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    This will allow you to copy the A1:C16 range anywhere on the worksheet and it will replicate the formulae, as shown in this pic:
    OFFSET to replicate absolute refs in two directions.jpg
    Here's the file showing it working. Note that it assumes that all the references in your post above remain relative, unless they have $ included.
    OFFSET to replicate absolute refs in two directions.xlsx

    Hope that helps - if not, we really need a sample file before we can go any further.

  8. #8
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL (COVID allowing)
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,403

    Re: Copy cell formulas in 2 directions (vertical and horizontal )

    Amendment to above, as I've just re-read that you want to drag B4 and C4 down to B14/C14:
    In B4:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    In C4:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Now those formulae will drag down fine, as shown here:
    OFFSET to replicate absolute refs in two directions v2.jpg

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] how do i copy vertical formulas to horizonal and keep same cell r.
    By opiedrake in forum Excel General
    Replies: 6
    Last Post: 03-04-2015, 07:50 AM
  2. [SOLVED] Horizontal to vertical dragging of formulas...
    By Finalfrontier1976 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-20-2013, 09:39 AM
  3. [SOLVED] Copy and paste from Vertical to Horizontal
    By mardskee in forum Excel General
    Replies: 5
    Last Post: 09-03-2012, 09:08 AM
  4. Vertical Copy to Horizontal paste
    By cbeckwith in forum Excel General
    Replies: 5
    Last Post: 08-31-2012, 05:13 PM
  5. Horizontal dates put in vertical formulas
    By bajo671 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-29-2012, 03:17 PM
  6. Copying formulas from horizontal to vertical
    By pmd in forum Excel General
    Replies: 6
    Last Post: 09-06-2011, 11:38 AM
  7. Copy horizontal row to vertical column
    By xcelisgr8 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-23-2007, 01:41 PM

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