display last entry in column

[smokenack]

I am planning a workbook that will contain two sheets. I want to be able to show in Sheet2 cell A1 the last entry in column A on Sheet1 etc.

thanks for looking

Nick

 

[smokenack]

column A is text
column B are dates
column C are names
column D are times
column E are place names as are column F
column G are numbers
column H are text
column I is text

all columns only contain dates, times , numbers or text. They are never mixed.

 

[Aladin Akyurek]

column A is text
column B are dates
column C are names
column D are times
column E are place names as are column F
column G are numbers
column H are text
column I is text

all columns only contain dates, times , numbers or text. They are never mixed.

Define the following names, using Insert|Name|Define...

BigStr as referring to:

=REPT("z",255)

BigNum as referring to:

=9.99999999999999E+307


Then...

Book8
	A	B	C	D	E	F
1	TEXT	DATE			Last Value	Pos Last Value
2	IKF	1/5/2005		N	GJH	14
3	LLA	12/10/2005		N-1	BMF	12
4	AOB	7/18/2005		N-2	FNF	11
5	JGJ	10/10/2005
6		12/18/2005
7	MOK	7/1/2005
8	OAF	9/21/2005
9		4/10/2005
10	HNB	8/17/2005
11	CBB	3/11/2005
12	FNF	4/19/2005
13	BMF	6/30/2005
14
15	GJH
Sheet1

Formulas for the TEXT column...

E2, copied down:

=INDEX($A$2:$A$65536,F2)

F2:

=MATCH(BigStr,$A$2:$A$65536)

F3, copied down:

=MATCH(BigStr,$A$2:INDEX($A$2:$A$65536,F2-1))

For the DATE column, replace BigStr with BigNum.

 

[smokenack]

Wonderful. Works perfectly. Thank you very much for your time.

Nick