Anyone good with excel. (linking cells)

[TheSmiler]

I'm sorting out an expenditure sheet in excel, I've used the developer tools for check boxes. Now what I'm trying to do is to work out how much I've got to spend depending if an item has been ticked.

An example of what I'm trying to do below. So the total left column at the moment is only a sum function which is selected. But I don't really want to do this every time I click an item off.

https://i.imgur.com/wDAwWgs.jpg

Can anyone help
____________________
CB125>CG125>GN125>ER5>K100RS>R1100RS>K100RS
A2 completed 23/07/15 Ready for the Golden Crisp Packet

[James83]

This is technically possible, but if the list is going to be remotely long, it'll be a pain to add the check boxes. You may well be better off with a simple 'Y' or 'N' written into the cell instead.

If you are committed to using the checkboxes, you first need to get the state out of it. Select a checkbox by ctrl-clicking it, then in the formula bar, put an = sign and then click the cell you want the value to go into. That cell will then show either TRUE or FALSE depending on if the box is ticked or not. What you could do is assign the checkbox values to the cell they are sitting over the top of in column D. If you set the font to white, you won't see them.

You then need to do a sumif to get the total, something like:
=sumif(D:D,TRUE,C:C)
____________________
Jonathan A wrote: tart.

[J.M.]

I assume you only want 1 answer for total cost and total left. Or do you want a breakdown of what total left and total cost was after each purchase?

One Total
Uses Y/N instead of checkbox. 500 is arbitrary starting budget.

Total Cost: =SUMIF(D,"=Y",C:C)
Total Left: =500-G2

Total After Each
Again uses Y/N. 500 is arbitrary starting budget. 120 is arbitrary total cost before starting spread sheet.

Initial total left: 500-SUMIF(D2,"=Y",C2)
Initial total cost: =120+SUMIF(D2,"=Y",C2)

Next total left: =F2-SUMIF(D3,"=Y",C3)
Next total cost: =G2+SUMIF(D3,"=Y",C3)

Then;
Every time you want to add a new item, or a bunch of them, select column F&G for the final row. There will be a blue square in the bottom-right of the bottom-right most selected sell. Drag it down to the next row.

Attachment may/may not work as I don't have office installed to test it.
____________________
2004 R1 & 2018 XSR900

[TheSmiler]

Changed it to the yes no statement and added an extra tab which is now coloured differently and locked. Worked out in the end. Now if I change a sum or change one to an Y then it all sorts itself out thank you.
____________________
CB125>CG125>GN125>ER5>K100RS>R1100RS>K100RS
A2 completed 23/07/15 Ready for the Golden Crisp Packet