Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie
Hi all! We have been experiencing an issue on site where threads have been missing the latest postings. The platform host Vanilla are working on this issue. A workaround that has been used by some is to navigate back from 1 to 10+ pages to re-sync the thread and this will then show the latest posts. Thanks, Mike.
Hi there,
There is an issue with role permissions that is being worked on at the moment.
If you are having trouble with access or permissions on regional forums please post here to get access: https://www.boards.ie/discussion/2058365403/you-do-not-have-permission-for-that#latest

Moving cells in excel maintaining formula

  • 13-09-2012 1:22pm
    #1
    PaudieP
    Registered Users Posts: 3 ✭


    I have a spreadsheet containing 12months, I need the months (rows) to roll as the spread sheet is updated with the new month while maintaining the formula and reference in each cell of the row,e.g

    Jan Feb Mar Apr May Jun Jul Aug Sept Oct Nov Dec (current)
    Feb Mar Apr May Jun Jul Aug Sept Oct Nov Dec Jan (update)

    Can anyone suggest the best way to complete this? Simple cut and paste won't work as it losses the reference to cells. Note: data is being pulled from another source to complete the calculations.


Comments

  • #2
    Diddy Kong
    Registered Users, Registered Users 2 Posts: 937 ✭✭✭Diddy Kong


    PaudieP wrote: »
    I have a spreadsheet containing 12months, I need the months (rows) to roll as the spread sheet is updated with the new month while maintaining the formula and reference in each cell of the row,e.g

    Jan Feb Mar Apr May Jun Jul Aug Sept Oct Nov Dec (current)
    Feb Mar Apr May Jun Jul Aug Sept Oct Nov Dec Jan (update)

    Can anyone suggest the best way to complete this? Simple cut and paste won't work as it losses the reference to cells. Note: data is being pulled from another source to complete the calculations.

    Copy -> Paste Special -> Formulas


  • #3
    PaudieP
    Registered Users Posts: 3 ✭PaudieP


    Thanks Diddy, Problem is I have additional calculations at in row M that can't be moved. I don't think my first description was accurate enough; does this make more sense....
    When I start a new month this is populated in row L and the previous month (which was occuping row L) is now moved to row K and so on for all other rows to A. while this is happening i need all formula to follow this, currently when i complete this a formula in say row B, =B3-A3 now reads B3-L3 as the row A has been moved to L as the new month. while maintain the SUM formula in row M.

    hopefully this better explains my problem


  • #4
    Sup08
    Registered Users, Registered Users 2 Posts: 1,674 ✭✭✭Sup08


    upload a sample workbook please.


  • #5
    fionny
    Registered Users, Registered Users 2 Posts: 2,367 ✭✭✭fionny


    If you want it to stay locked to a Column or a Row regardless you can use the $ in the formula

    so =$B$4-$C$4 will stay the same if you paste it along or down it was always calc off the same column and row.

    if you did

    =$B4-$C4 it will hold the column but increment the row

    and

    =B$4-C$4 will hold the row but increment the column.

    For a quick way of doing the lock you can select the part of the formula and hit f4 on the keyboard (this locks both elements)

    Hope this is what you were after.


Advertisement