Calling all accountants!!

[Lammy]

I assume you are all DIVAS at Excell?

Well I want to create a spreadsheet to hold information about a given bank account.

Basically I have one account to hold money for lots of different things, e.g. food and petrol.

I will be paying in a set amount each month and removing the money as and when I need it. However, I would like to track how much money I have for food and how much I have for petrol.

I could edit the spreadsheet manually once a month, when I pay the money in and also everytime I take some out.

My question is this;

Is it possible to have the spreadsheet update itself once a month when the money goes in? Or do I have to do it manually?

thank you.

 

[tedebear]

Depends on where the data is coming from?

 

[Lammy]

Depends on where the data is coming from?

It's just a number in a cell. It's nothing clever.

All I want is to say = (if date = x then add £100)

or something.

 

[tedebear]

It's just a number in a cell. It's nothing clever.

All I want is to say = (if date = x then add £100)

or something.

well yes of course it can do that - I thought you wanted to load your bank balance every month and then split it out into your expenses and then see whats left over?

 

[Lammy]

well yes of course it can do that - I thought you wanted to load your bank balance every month and then split it out into your expenses and then see whats left over?

Wow that sounds good!! How do I do that?!?!?

Failing that, how do I do the simple version

 

[tedebear]

Depends what you want to do really...

I keep a spreadsheet to track each months bills. Our online banking has a facility to download the statement in data format and I open it up in excel and as long as all the direct debit vendors names are the same, I've got my spreadsheet set up to then plonk the bill under each category. However - thats a month in arrears to what you want to do I think?

What you could easily do without a download is on the day you get paid, plop that number in a particular cell, and then have some formulas set up to split that number into your expected petrol, food etc. expenses....then you can see what you'll have left after your expected expenses?

 

[Lammy]

Depends what you want to do really...

I keep a spreadsheet to track each months bills. Our online banking has a facility to download the statement in data format and I open it up in excel and as long as all the direct debit vendors names are the same, I've got my spreadsheet set up to then plonk the bill under each category. However - thats a month in arrears to what you want to do I think?

What you could easily do without a download is on the day you get paid, plop that number in a particular cell, and then have some formulas set up to split that number into your expected petrol, food etc. expenses....then you can see what you'll have left after your expected expenses?

So I would still need to enter the amount daily.

What I'm after is a spread sheet that know how much I've been paid and when. This is a constant. All I should need to do is update how much has been spent each month on food and petrol for example.

 

[Beach Hut]

So I would still need to enter the amount daily.

What I'm after is a spread sheet that know how much I've been paid and when. This is a constant. All I should need to do is update how much has been spent each month on food and petrol for example.

Have you got enough money left over to pay a consultant to do this for you

 

[tedebear]

So I would still need to enter the amount daily.

What I'm after is a spread sheet that know how much I've been paid and when. This is a constant. All I should need to do is update how much has been spent each month on food and petrol for example.

You get paid daily? WOW - can I have your job?

Unless you have a direct feed from your bank account - you'll need to do this all manually. ie each time you deposit into your account and each time you spend. If the deposit amount is always the same then you'll not need to touch that cell.

At the beginning of the month you could split the deposit amount out into "expected" spend and, then, when you "actually" spend it - enter that number...giving you what's left over...

 

[Tesco in Disguise]

I keep a spreadsheet to track each months bills. Our online banking has a facility to download the statement in data format and I open it up in excel

nerd alert!

 

[tedebear]

nerd alert!

Excel is my life

 

[maffew]

It's just a number in a cell. It's nothing clever.

All I want is to say = (if date = x then add £100)

or something.

A1 A2
Old Balance New Balance
1000 1100

This would display as an extra 100 quid if its the first of the month not sure if its quite what you are after

=IF(DAY(NOW())=1,A2,A2+100)

 

[Lammy]

A1 A2
Old Balance New Balance
1000 1100

This would display as an extra 100 quid if its the first of the month not sure if its quite what you are after

=IF(DAY(NOW())=1,A2,A2+100)

Good man!

 

[Beach Hut]

Excel is my life

Suggest you get out more

 

[tedebear]

Suggest you get out more

I know

 

[tedebear]

A1 A2
Old Balance New Balance
1000 1100

This would display as an extra 100 quid if its the first of the month not sure if its quite what you are after

=IF(DAY(NOW())=1,A2,A2+100)

I was trying to talk him out of doing that - but fair enough - seems to be what he wants...

 

[Lammy]

I was trying to talk him out of doing that - but fair enough - seems to be what he wants...

why?

 

[tedebear]

why?

because it won't tie up to your bank account, public hols etc. maybe I'm getting a bit too detailed though.

 

[maffew]

If you know the amounts in advance which it sounds like you do you could just do a simple lookup which will update depending on the day it is

so in Sheet1 A1 type
= now()

in Sheet1 B2 type
=VLOOKUP(INT(A1),Sheet2!$A$1:$B$11,2,FALSE)

in Sheet2 A1 Paste these values

date Value
14/05/2007 100
15/05/2007 200
16/05/2007 300
17/05/2007 400
18/05/2007 500
19/05/2007 600
20/05/2007 700
21/05/2007 800
22/05/2007 900
23/05/2007 1000
24/05/2007 1100

or something?

 

[Publius Ovidius]

http://office.microsoft.com/en-us/templates/results.aspx?qu=family+budget&av=TPL000


have a look at these.

They can be customised, but that may help