# Google Sheets: Formula not copying across properly

1. ## 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?

2. ## 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

4. ## Re: Formula not copying across properly

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

5. ## Re: Formula not copying across properly

Originally Posted by Fluff13
Maybe
Formula:
`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. ## 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. ## 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!

8. ## 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.

9. ## 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.

10. ## Re: Is this Formula Possible? (bumped)

I think you're right!

It's exactly as envisioned. Thank you!

Incredible work.

11. ## 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.

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

#### 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