Credit Card Statement (AutoUpdate)

[martialtiger]

Here's a complex spreadsheet that I am trying to come up with.

What I want to do is have a sheet that has 5 columns:

1st column is the date and I want this to autoupdate to the curren
date. So in my sheet I'm having today as the first day, then the nex
row will have 3/31/04 automatically inputted.

2nd column is the any debits made and 3rd column is credits made
Nothing needed on this.

4th column is the beginning balance of the day.

5th column is the ending balance which includes the finance charge an
any credits/debits.

Now on the top of the sheet I have a cell that is for Current balanc
and I want this to auto update and show the most current balance as o
the current date.

Anyone have any ideas how to do this? :confused

 

[Norman Harker]

Hi Martialtiger!

If your first entry is in E4 and assuming that the list can't go below
E100 and that there are no entries below the final balance in E:

=OFFSET($E$4,COUNTA($E4:$E100)-1,0)

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[martialtiger]

Thanks Norman,

That solves the Current Balance cell. Now is there a way to have th
dates auto-populate each passing day? That way I can have the endin
balance column(E) only show a balance for days that have already passe
(including current day). I hope this makes sense

 

[Norman Harker]

Hi Martialtiger!

With a data entry in A4 of the first date

A5:
=IF(A4<TODAY(),A4+1,"")
Copy down.

However, that produces problems with the display of the balance
columns.

I'd get round that by using conditional formatting of those columns.

Select the entire potential range covered by the balances.
Format > Conditional Format
Formula Is:
=$A4=""
Format button
Select font colour the same as background colour.
OK
OK

The balances continue to roll forward for non-displayed dates at the
same amount as the current date but they are not displayed.

You might want to see my version of what you need. But I haven't done
any interest calculations. They might complicate things but it's not
impossible.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[martialtiger]

Norman,

Thanks again. I would love to see your version of what I'm trying t
get done. I appreciate all your help

 

[Norman Harker]

Hi Martialtiger!

e-mail the address below. It's very basic but covers the questions
you've raised; so far

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.