Software

General discussion

Gravatar
Locked

Excel help

By skat ·
How can I reference (link), from an Excel 2000 cell, the last used cell in a column.

For example, a spreadsheet has rows of entries of debits and credits. A new balance is calculated in the Balance column for each row. In another sheet I want to reference the most recent balance (last used cell in the Balance column).

How do I write the link?

This conversation is currently closed to new comments.

2 total posts (Page 1 of 1)  
+ Follow this Discussion ·
| Thread display: Collapse - | Expand +

All Comments

gravatar
Collapse -

by glyall In reply to Excel help

the sum from sheet the first sheet must be include in the second sheet. Example first sheet

=sum(a1..a5) in cell a7
on the second sheet
=sum(sheet1.a6) this cell location does not matter.

you can e-mail a test spreadsheet and I will try
to show you how to do it if you can need fgiure is out

e-mail to glyall@iowatelecom.net

Good Luck

gravatar
Collapse -

by davidfranks78 In reply to Excel help

Following assumptions:
1. Your "balance" column is column "J"
2. You are currently having 10 rows
3. The 1st row contains the column headers
4. Rows 2 to 11 contains the data rows
5. The worksheet is "sheet1"

Then the following formula will give the last column value in column J

=OFFSET(Sheet1!K1,(COUNTA(Sheet1!J2:Sheet1!J11)-1),-1)

Please note:

The formula will not work if any blank entry is there in the "Balance" column

As with all excel formulas, in order for the "CountA" formula to update automatically, to add a row, you need to insert a row before the last row (within the range). If you add a new row after the last row (outside the range) then you will need to manually update the reference in the "CountA" range.

Back to Software Forum
2 total posts (Page 1 of 1)  

Start or search

Related Forums