Cell Reference Increasing too much

Hi Everybody

I hope you are all doing well and keeping safe with everything going on.

I hope you can help me as I have been struggling for a few hours now.

I basically have a sheet that is pulling in data from another sheet. Underneath one row I have a duplicate row with the same information as the row above however one of my cells has a negative figure compared to the one above which has a positive. Its kind of like a credit note against an invoice - one balances out the other.

What I was trying was copying the two rows down all the way down my sheet however when I copy two down, it jumps the previous cell reference by 2 and not by 1 which I require. I have tried using the offset function, however my offset ends up falling behind as it doesn't increase incrementally by 1 as I copy down. I hope I have explained my situation well enough. I have tried to showcase the problem below:


(DATA PULLED FROM OTHER SHEET)

D7='SHEET1'!H8
D8='SHEET1'!H8*-1

I then select those two rows and drag down to D9 and D10 and it does this:

D9='SHEET1'!H10
D10='SHEET1'!H10*-1


But instead of referring to cell reference 'SHEET1'!H9 it refers to cell 'SHEET1'!H10 - I assume this is because I am copying down from two rows originally but I need both rows as one acts as the positive line and the other as the negative. I just want the cell reference increment to be by 1 instead of 2 so as it goes down it refers to H9, H10, H11, H12 etc.

Thank you in advance for your help.

Originally Posted by Paul

This formula may help...

Formula:

Please Login or Register  to view this content.

For the second (negative value) cell below it, just add a - in front of the formula, e.g.

Formula:

Please Login or Register  to view this content.

When you select both of those cells, then drag down, it should work properly.

Thank you Paul.

I had to re-implement the formula for my specific data set but it appears to be now working as I had hoped. Thanks for your help!