Software

Ask something
Discussion Locked

Excel help

0 helpful
2 replies

by skat

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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?

2 replies

by glyall

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

by davidfranks78

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.

This discussion is closed to new replies.