+ Reply to Thread
Results 1 to 11 of 11

Google Sheets: Formula not copying across properly

  1. #1
    Registered User
    Join Date
    03-20-2022
    Location
    England
    MS-Off Ver
    Sheets
    Posts
    5

    Google Sheets: Formula not copying across properly

    Hello!

    I am making a budgeting spreadsheet, showing finance/credit taken out, and how it reduces over time.

    I have a cell for the amount of credit "3,455.28" (C4), a cell for the monthly repayment "98.72" (D5) and cells E4 onwards to show monthly values.

    I use cell E2 to show the monthly value being 'paid', this is something I would manually enter a value for to update the spreadsheet.

    Formula is as shows:

    FOR CELL E4: =IF(E2>0,C4-D4,C4). With a number 1 in E2, the value will be adjusted to show 3,356.56, without a 1 it would show the value in C4.

    FOR CELL F4: =IF(F2>0,E4-D4,E4) - Same as the above but with the value 2 in F2.

    FOR CELL G4: =IF(G2>0,F4-D4,F4) - Same as above but value 3 in G2.

    Now comes the problem, if I highlight cell F4 and G4, copy it across (stretch the formula), the formula in cell H4 becomes =IF(H2>0,G4-F4,G4). Entering 4 in H2 would give a value -98.72.

    The formula works with manual inputs, but when copying across, it glitches as described. When copying down, it would change (for cell E5) E3, instead of E2, for the manual input.

    I'm having a complete nightmare with it, what am I doing wrong?

    Please help!
    Last edited by AliGW; 03-25-2022 at 12:43 PM. Reason: Please do NOT post-edit thread titles in this way!!!

  2. #2
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    6,546

    Re: Formula not copying across properly

    Hi and welcome. Could you attach a sample workbook showing your data layout and formulas along with some idea of expected results?
    Always easier to assist if we can see the issue in context.

    BSB

  3. #3
    Registered User
    Join Date
    03-20-2022
    Location
    England
    MS-Off Ver
    Sheets
    Posts
    5

    Re: Formula not copying across properly

    Edit: Example spreadsheet attached.
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    12,081

    Re: Formula not copying across properly

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

  5. #5
    Registered User
    Join Date
    03-20-2022
    Location
    England
    MS-Off Ver
    Sheets
    Posts
    5

    Re: Formula not copying across properly

    Quote Originally Posted by Fluff13 View Post
    Maybe
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Wow! This did the trick straight away!

    If I could delve in, what difference does the $ make in this situation?

    Incredibly grateful, I've been hitting my head against a wall for hours on this.

  6. #6
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    12,081

    Re: Formula not copying across properly

    The $ signs lock the row/column depending on where they're placed. Have a look here for more info https://www.ablebits.com/office-addi...ference-excel/

  7. #7
    Registered User
    Join Date
    03-20-2022
    Location
    England
    MS-Off Ver
    Sheets
    Posts
    5

    Re: Formula not copying across properly

    If I could give this a bump, working on the same spreadsheet (attached).


    Page - Credit.

    Background - I'd like F column to be a 'starting' balance and G to be a running balance. Page monthly balance will be that running balance (manually input a reducing figure).

    Is there any way to do a formula to show this? I was trying to work an IF forumula trying to do if the number is >the starting balance, but wasn't sure how to do it on a row for another spreadsheet?

    Not sure if this one is possible, please let me know!

    Many thanks in advance,
    Attached Files Attached Files

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,084

    Re: Is this Formula Possible? (bumped)

    It is easier to help when we have actual data (typed manually if necessary) with which to work.
    I made up some values and did some rearranging on the Monthly Balance sheet, switched Monthly Payment and Opening Balance, to come up with that which I feel you are looking.
    The formula for rows 4:5 on the Monthly Balance sheet is: =IF(E$2="x",$D4-$C4*COLUMNS($E4:E4),D4)
    The formula for Balance on the Credit sheet is: =B3-C3
    The formula for the Total Outstanding column: ='Monthly Balance'!BH4
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,647

    Re: Formula not copying across properly

    You should have started a new thread for this.

    Do NOT change the title of a thread unless a moderator asks you to do so.

    Do NOT ask a new question in an old thread - I will let it go THIS time.

    I am moving this to the correct forum section as it appears to be about Google Sheets, not Excel.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  10. #10
    Registered User
    Join Date
    03-20-2022
    Location
    England
    MS-Off Ver
    Sheets
    Posts
    5

    Re: Is this Formula Possible? (bumped)

    I think you're right!

    It's exactly as envisioned. Thank you!

    Incredible work.

  11. #11
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,084

    Re: Google Sheets: Formula not copying across properly

    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

+ 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. Google Sheets. Can I connect offline data from excel to google sheets?
    By drlemur39 in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 3
    Last Post: 11-15-2021, 10:40 AM
  2. (Google App Scripts) Copy and paste value for all Google Sheets contained in one folder
    By bobbiekan in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 4
    Last Post: 09-29-2021, 11:48 PM
  3. [SOLVED] Google sheets script to download file and save it to google drive folder and use it's data
    By western in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 5
    Last Post: 01-18-2021, 01:31 PM
  4. [SOLVED] Google sheets vba to download file and save it to google drive folder and use it's data
    By western in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-14-2021, 08:48 AM
  5. dynamic formula to count through existing and new sheets(In google sheets)
    By wlinksanju in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 1
    Last Post: 11-26-2020, 09:16 PM
  6. Conditional formatting not copying properly to separate sheets
    By Keegan1116 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-25-2020, 09:48 AM
  7. Google Apps Script for Google Sheets Pulling Formulas from Master to Several Slave Sheets
    By excelroofing in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 4
    Last Post: 08-22-2018, 02:06 AM

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