How to keep a cells sum value constant while changing the other cell values

[asabur6]

I have Excel 2010. Is there a formula for the following? Here is my example:

[pre]

116	Bill 1	165
Bill 2	160
Bill 3	170
Bill 4	26
Hshold	115		752

[/pre]
752 is the SUM of all the values. If I manually change the 116 and/or one or more of the Bills values, AND I want only the Hshold value to auto adjust to keep the 752 sum constant.

 

[Luke M]

If the values change, but the formula doesn't update, then wouldn't it be showing incorrect info?

you could copy the 752 formula and then do a paste special - values to make it a constant.

Perhaps you could post ax example of the "after" image of what you want?

 

[asabur6]

@ Luke M


My thinking is that I want only the Hshold value would Auto Adjust to keep the 752 sum.


So for instance if I changed Bill-1 to 170 (up by 5), then only HsHold would Auto adjust to 110 (down by 5), to still keep the total SUM 752.

 

[Luke M]

If that's the logic, then I think the better setup would be to make 752 a constant, have have Hshold formula be something like

=Total-SUM(Bills)

 

[asabur6]

Thanks! it worked I entered the formula for Hshold as =752-SUM(B20,D20,D21,D22,D23). I figured it was something simple. Iam a novice user at Excel and dont use it frequently.


I have another question, Let me know if I should I post it or would you be able answer it?:

How can I insert a "last updated date and time" into any cell to automatically update each time anything in the entire workbook is changed? If it deals with macros or vb, Iam not familiar with those so I would need step by step instructions.


Thanks so much for helping me.

 

[SirJB7]

Hi, asabur6!


- From the Excel worksheet press Alt-F11 to go to the VBA editor

- Double click on the related worksheet at the top left treeview panel

- Go to the big top right panel and paste this code:


-----

[pre]

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Range("A1").Value = Now()
Application.EnableEvents = True
End Sub

[/pre]
-----


- Press Alt-F11 again to return to Excel


Anytime you modify a cell in that worksheet, A1 will store the date and time stamp.


Regards!

 

[asabur6]

Thank you for your help. However, it doesnt show up on my sheet. I would like to have the date and time visable in a cell on the sheet.

 

[SirJB7]

Hi, asabur6!

Consider uploading a sample sheet specifying requirements and examples of desired output.

Regards!

 

[Luke M]

Is the font in A1 formatted to a visible color?

 

[SirJB7]

@Luke M

You're right... should I add: isn't cell A1 wide enough to be displayed? is column A hidden? is row 1 hidden? which cell format has A1?

Regards!

 

[Luke M]

@SirJB7,

I know you're laughing, but I've seen it before. =P