Excel Question

[cshack]

Hello,
I have a column full of information - to which I'm constantly adding more information to. Is there a function which will tell me what is in the last cell of the column i.e. an "if" or "=".
Example:
Column A - Rows 2 to 5,000 are full of information. I need to know what is in the last cell of column A and display it in cell 1A.

 

[Hasit]

Take a look at this:

http://www.j-walk.com/ss/excel/tips/tip30.htm

 

[cshack]

Thanks Hasit,
Do you need any plug-ins for that as I only have '97 version. I've put the formula in (=lastincolumn (A)), but I get the "name" error in the cell.

 

[xlbo]

Can you not just use = Counta(A:A)
This will count the number of entries in column A - this will also be your last row if it is a continuous set of data
HTH
Geoff

 

[cshack]

Using =counta(a:a) as you know - just counts the cells, it would work as long as the information is 1, 2, 3, 4 etc. but if the information is erratic the last cell information would be incorrect.

 

[Hasit]

Geoff,

cshack is after the contents of the last cell in the column. The counta command will only tell you the number of columns that have entries (if they are continuous as you say).

The function in the link above works for me and I am on '97 as well. You should/do not need an add-in. What I am trying to work out is a formulaic method of getting the data. I almost have the answer......

 

[xlbo]

Apologies - I misread the question
Enter this in a module attached to the workbook
Function LastCellData()
Application.Volatile
lrow = Range("A65536&quot.End(xlUp).Row
LastCellData = Range("A" & lrow).Text
End Function
then enter =LastCellData() in B1

Unless changing the last cell causes a calculation, you'll need to press F9 to update it.
HTH
Geoff

 

[Hasit]

At last!!!

Heres the formula way of getting the result you need:

=INDIRECT(ADDRESS(MAX((DataRange<>&quot;&quot*ROW(DataRange)),
COLUMN(DataRange),4))

Use the formula above and make sure its an array formula (i.e. when you enter the formula, hit CTRL-SHIFT-ENTER to get {} brackets around it).

If you paste this formula in A1, and then highlight the area you want to test for (i.e. A2:A65536), then hit CTRL-F3 (function key 3), you will get a define name box . Type in &quot;DataRange&quot;, hit OK, and it should return the contents of the last cell in column A.

 

[xlbo]

Nice formula - but....the name will already be in place the next time and therefore won't prompt to define it.If the range isn't dynamic(which it isn't and AFAIK can't be without continuous data), then adding data to the end will not increase the range and you will always get the same answer, even though new rows have been added.

However - harcoding to the rescue -
=INDIRECT(ADDRESS(MAX((A2:A65536<>&quot;&quot*ROW(A2:A65536)),
COLUMN(A2:A65536),4)) - array entered will work. As I said, nice formula Hasit
Geoff

 

[Hasit]

Good point Geoff. But. If you have named the range between A2:A65536, then any additions to the end of the data in the column (i.e. if the last entry is in A47 and you add one to A48), the formula will automatically update the result.

I checked to see if this is true and it does work.

However, point taken on the hardcoding.

 

[Guest_imported]

If as you said, &quot;Column A - Rows 2 to 5,000 are full of information&quot; with no empty cells, you cuold try the following:

=offset(A2,CountA(A2:A65536),0,1,1)

Hope you find this usefull.