[SOLVED] Possible sum function of cells, with moving ranges?

[tobbyluki]

Thank you for reading, and choosing to help, if possible. I'm pretty new to spreadsheets in general. I have a simple budget calc. sheet I've set up, to help keep track of which bills I pay out of which check of the month. Let me explain...I get paid every other Friday, so most months I have 2 checks (1st check and 2nd check of the month) but those dates move. I have a formula that totals the bills I need to pay out of the first check, and the second check, which gives me a number I need to set aside. So, my question would be, is there a way to automatically have the formula change based on where the rows are highlighted? The highlighted cells have a sum formula in each.

[FJCC]

Sorry, I don't understand your question "is there a way to automatically have the formula change based on where the rows are highlighted?" Do you mean that you have changed the formatting of the cells, e.g. changing the color, and you want the formula to take that into account. For example, sum only the yellow cells. That cannot be done in OpenOffice. If that is not what you mean, what do you mean by "where the rows are highlighted"?
Uploading an example file would be very useful. You can change the content so you don't post your personal information, we just need to see the layout and examples of how you want the formulas to behave. To upload a file, click Post Reply and look for the Upload Attachment tab just below the box where you type a response.

[tobbyluki]

Thank you for the reply. I've attached an example of the sheet I use. My next 2 paydays are on Friday, Sept. 21st and Friday Oct 5th. The light blue highlighted rows denote 'payday' meaning anything due on or after that date should be paid on check one, and also the same for check 2. So, tomorrow, I should pay Electric, cc3, sprint, direct tv, xfinity, and cc 4 and 5. Hopefully, that makes sense. So, below, I have a sum function that tallies those boxes. My conundrum is, my due dates stay the same, however my pay dates change monthly (so I manually move my highlighted rows to help keep track of what must be paid out of each check.) Can I change my formula to sum 'all cells below color blue until the next color change' or something similar? It's not a big deal, but I figured since I was learning, I'd try to make it exactly how I want it to be.

Thank you again for helping an old dog w/ hopefully some new tricks.

[FJCC]

I am not able to think of a good solution to your problem using one block of cells as you have in your example. I suggest using something like what I put on Sheet2 of the attached file. The columns for Creditor, Due Date and Min. Payment appear twice because some dates have two bills due. The blue rows are produced with a conditional format that you can see with the menu Format -> Conditional Format after clicking on one of the blue cells. By having all of the calendar days on the sheet it is easy to produce the two week pay pattern. Most of the expenses can be simply copied from month to month in a block, using Paste Special to avoid pasting over the conditional formatting. I assumed your Xfinity bill is due on the last day of the month, so I copied that in by hand for each month. This method has the advantage that you can see what you paid in the past and what is coming up and it isn't bothered by months that have three pay checks. Might this work for you?

[tobbyluki]

Thank you. This should do the trick!