Advice for approaching same goal but a different way

[ipbr21054]

Afternoon,
I am using the code below but have an issue with it so need to get advice to approach it from another angle.
Currently the code is in cell K33 BUT as soon as figures come into play on this cell the formula is gone.

Please advise how i can either put the code in another cell BUT have the figure still shown in cell K33 or advise.

Thanks

=IF(D4="","",SUM('INCOME (2)'!G32 -'EXPENSES (3)'!K32)-MILEAGE!C32)

 

[Joe4]

Excel formulas can only return values to the cells that they exist in. They cannot return values to "other" cells.
To do something like what you want, may require VBA.

Why/how is the formula being overwritten?
How is the data being put into this sheet.

By the way, the SUM function is totally unnecessary in your formula. You use SUM to add up a whole range of cells, i.e.
=SUM(A1:Z1)
If you are manually typing in the "+" and "-" signs, there is no need for it.
Just use:

=IF(D4="","",'INCOME (2)'!G32 -'EXPENSES (3)'!K32 - MILEAGE!C32)

 

[ipbr21054]

Hi,
Values are copied from various cells on a worksheet.
When that sheet is full / complete a command button copies these values & then pastes then into various cells on the next worksheet.

This is when the formula that was in the cell gets removed & a value like say £1523.55 is then entered.
I assumed / liked it to have the formula & the £ value there but that is not the case here.

Example of copy / paste code

Private Sub CommandButton1_Click()
  Sheets("EXPENSES (4)").Range("D4").Value = Sheets("EXPENSES (3)").Range("D30").Value
  Sheets("EXPENSES (4)").Range("F4:K4").Value = Sheets("EXPENSES (3)").Range("F30:K30").Value
  Sheets("EXPENSES (4)").Activate
  ActiveSheet.Range("A5").Select
  If Sheets("EXPENSES (4)").Range("K32").Value <> Sheets("EXPENSES (3)").Range("K32").Value Then MsgBox "Balance of sheets incorrect", vbCritical, "K32 CELLS DO NOT MATCH"
End Sub

Basically its just a running total of my income / expenses at a glance.

 

[Joe4]

You say this formula is in cell K33, and it is on the "EXPENSES (4)" sheet I presume?
I see nothing in your code that would be overwriting cell K33.

 

[ipbr21054]

Typo K32 but same for expenwses1,2,3 etc etc

 

[Joe4]

Typo K32 but same for expenwses1,2,3 etc etc

Their still doesn't appear to be anything in that code that would be overwriting cell K32.
So I am very confused as to what is happening here, unless you have left some code or other important information off.

 

[ipbr21054]

Nope thats it.

I see the formula.
Values are copied from say EXPENSES 2 and pasted on worksheet EXPENSES 3 the cell where the formula was now shows example £123.45
Looking in address bar the formula is gone.

I moved the formula to another cell whilst wait for a reply & it works when i then do it.
Save then open & still there.

When i print though this cell is then outside of my print area.
So any reason as to why it dont like K32 ?

Thanks

 

[Joe4]

Values are copied from say EXPENSES 2 and pasted on worksheet EXPENSES 3 the cell where the formula was now shows example £123.45

There is nothing in your code that does that, so is that a step that you are doing manually?
If so, exactly what range are you copying over from EXPENSES 2 to EXPENSES 3?
And then is this "K32" cell with the formula you are talking about actually on EXPENSES 3 and not EXPENSES 4?

 

[ipbr21054]

The formula is on each expenses worksheet in cell K32

The code to copy / paste is as post # 3

Once thats been done formula in K32 is gone

 

[Joe4]

I think we are going to need to access a copy of this workbook to see what is going on.
Are you able to upload a copy, with any sensitive information removed, so that we can take a look at it for ourselves?
Simply upload it to a file sharing site and provide a link to it here.