I have a spreadsheet of my regular monthly expenses. In general it is the same list of bills every month, but there are occasional additions and the due dates change on many of them. Along with the name of the expense I also log the date it is due, the amount due, the date I pay it, the amount I pay, how I paid it, and some general info about the credit cards on the list like interest rates and balances, etc so we can track those if they change.
What I'd like to do is to be able to copy this as a separate worksheet within 1 workbook for each month. Then, for each month, be able to input the new due dates and amounts, sort by due date, and have it automatically calculate several different totals and averages for me from the new data. The problem is that the due dates change so I would have to re-write the equations every time an expense moved in sequence in the list. (If you write the formulas then sort the formulas track the same cells, the cell addresses do not adjust with the sort.) I'm trying to find a way to tell it to find a cell by looking at the labels (or to follow a cell that I label) and use that data in the calculation. The problem is that I can't seem to figure out how to get it to follow or lookup the data within the calculation.
Currently I have named each row and column by its label (Insert > Label > Create). Now I'm trying to figure out how to write a formula using 2 names as a coordinate. So far all the examples I've found only use 1 of the names, either the row or the column. I want it to lookup the data in a single cell based on the row and column labels, then sum a series of those. I just haven't figured out how to write the formula yet. Or...it isn't possible in this program. Thanks, in advance.
_________________
Insert bandwith sucking sig here.
This message was edited by MadDoc on 2002-10-12 15:34
This message was edited by MadDoc on 2002-10-12 15:34
What I'd like to do is to be able to copy this as a separate worksheet within 1 workbook for each month. Then, for each month, be able to input the new due dates and amounts, sort by due date, and have it automatically calculate several different totals and averages for me from the new data. The problem is that the due dates change so I would have to re-write the equations every time an expense moved in sequence in the list. (If you write the formulas then sort the formulas track the same cells, the cell addresses do not adjust with the sort.) I'm trying to find a way to tell it to find a cell by looking at the labels (or to follow a cell that I label) and use that data in the calculation. The problem is that I can't seem to figure out how to get it to follow or lookup the data within the calculation.
Currently I have named each row and column by its label (Insert > Label > Create). Now I'm trying to figure out how to write a formula using 2 names as a coordinate. So far all the examples I've found only use 1 of the names, either the row or the column. I want it to lookup the data in a single cell based on the row and column labels, then sum a series of those. I just haven't figured out how to write the formula yet. Or...it isn't possible in this program. Thanks, in advance.
_________________
Insert bandwith sucking sig here.
This message was edited by MadDoc on 2002-10-12 15:34
This message was edited by MadDoc on 2002-10-12 15:34