# Cell Referencing

#### Tacomanator

##### New Member
Hi, I am trying to create a ledger in excel that looks like this:

Date | Type | Description | Amount | Balance

The first four fields should be entered manually, but I would like the balance to be calculated automatically using the sum of the previous balance and the amount entered. Follow?

1. So far from my messing around it seems like you have to have a function tied to a cell, therefore, how do I tell it that this entire column needs to do the same thing?
2. How do I reference cells such as, the cell directly above me and the cell directly to the left of me?

TIA

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

##### MrExcel MVP
You could provide a 5 row sample with results in the balance column.

#### Corticus

##### Well-known Member
Welcome,

I wasn't sure if this was a template for accounts payable or receivable, but really it doesn't matter, payments are positive expenses, and receipts are positive balance entries.

Anyways, for the balance to calculate correctly, copy and paste it down the column, the cell references will update accordingly:
Book2
ABCDE
1DateTypeDescriptionAmountBalance
21/1/2002CashCreditCard100100
31/2/200250150
41/3/2002200350
51/4/200275425
61/5/2002
Sheet1

HTH,
Corticus

#### Tacomanator

##### New Member

I would like to add a field at the bottom that totals, for example, the 'amount' field for every row that has a type of 'debit'. If I was writing code I would say:

foreach my \$row (@rows) {
\$total = \$total + \$row->{amount} if \$row->{type} == 'debit';
}

(for those of you who know PERL, I know the above is not quite right, but I wanted to make it more readable for those who do not).

TIA

#### kskinne

##### Well-known Member

use a sumif formula, for example:

if B1:B5 houses your 'types', and E1:E5 houses your amounts, you would use the following formula in the cell where you want this total:

=sumif(B1:B5,"Debit",E1:E5)

hth
kevin
This message was edited by kskinne on 2002-10-23 17:51

#### SamS

##### Well-known Member
Corticus, could you please explain why the LEN component in the IF statement, with thanks.

#### Tacomanator

##### New Member

Thanks Kevin, that helped very much, I put it in an it worked, but it didnt update it self when I added a new row, so I experimented and I came up with this (better version, I think) instead:

=SUMIF(B:B,"Debit",D:D)

That way, it is not dependant on having a set amount of rows. Thanks again to all who have helped, I have added some additional features on top of that based on your help!

L

#### Legacy 11273

##### Guest
Regarding the original question about a formula for a running balance, an alternative to Corticus's suggestion is to put the following in E2 and fill down :-

=SUM(D\$2:D2)

#### Corticus

##### Well-known Member
Hey,

Sorry for the delayed response, this **** work thing...

Anyways, I used len() to test for blanks, so the balance doesn't keep displaying all the way down the column. Len() tests for length, so if the cell's length is zero(blank) the formula displays nothing.

You could say If(A1="") instead of If(Len(A1)=0) but Aladin said to use Len(), so I do.

Peace,
Corticus

Replies
3
Views
233
Replies
0
Views
175
Replies
6
Views
210
Replies
2
Views
1K
Replies
4
Views
129

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,785
Messages
5,833,693
Members
430,224
Latest member
Tirrazo

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back