need formula to retrieve word value from last cell in column

[chaos]

Hi everyone,

I use the following formula to retrieve the number value in a cell that is in the last row of a column. Every day, a new number is added to the bottom of the column. I need the foumula to update dynamically, and this works find. Here is an example:

=INDEX(Data2!H:H,MATCH(BigNum,Data2!H:H)-0):INDEX(Data2!H:H,MATCH(BigNum,Data2!H:H)-0)

But I have another column with word values, rather than number values, that update in the same way, and this formula doesn't work. Could anyone tell me if there is a different formula available that will work with word values.

Many thanks in advance.

chaos

 

[PaddyD]

Hi.

see the example:

Book5
	A	B	C	D	E
1	Nums	Text		LastNum	18
2	1	a		Lasttext	m
3	2	b
4	3	r
5	233	d
6	2	y
7	14	vf
8	5	e
9	7	r
10	4	hg
11	22	p
12	2	d
13	2
14		e
15	18	m
16
Sheet1

the text version is:

=INDEX(B:B,MATCH(REPT("Z",10),B:B,1),1)

paddy

 

[Juan Pablo González]

Use the same formula, but define

BigNum2

as

=REPT("z", 255)

and use BigNum2 instead of BigNum (And change the ranges....)

 

[chaos]

:D :D :D
jeepers, you guys are fast. this board is amazing!

all the best,

chaos

 

[PaddyD]

an afterthought:

=INDEX(Data2!H:H,MATCH(BigNum,Data2!H:H)-0):INDEX(Data2!H:H,MATCH(BigNum,Data2!H:H)-0)


I'm guessing that you originally used this formula to define a dynamic named range, and have modified it for the current task. You only need 1/2 the formula to do the "return last value" task:

1) as per the help file, index() "Returns a value or the reference to a value from within a table or range. ". In this case you only need the value, so no need for the longer formulation
2) whatever you're doing, the -0 bits are superfluous!

paddy

 

[Yogi Anand]

Adding to Paddy's and Juan's contributions, if you need a single formula to pick the last tentry in column A regardless of whether it is text or numeric, you may want to try using the formula:

=INDEX(A:A,MAX(MATCH(9.99999999999999E+307,A:A),MATCH(REPT("z",255),A:A)))

see its application in the following simulation ...

y030503h1.xls
	A	B	C	D
1	CellEntry	LastCellEntryinColumnAregardless whetheritistextornumeric	m
2	1
3	p
4	g
5	233
6	2
7	14
8	5
9	7
10	4
11	22
12	2
13	2
14	e
15	m
Sheet7

I hope this helps. If I have misunderstood your question, please accept my apologies!

 

[Dave Patton]

'
easiest is

=LASTROW(B:B)

This works with numbers, text, or True/False

from Morefunc add-in.

see Add-ins information near top of list of messages

 

[Aladin Akyurek]

Use the same formula, but define

BigNum2

as

=REPT("z", 255)

and use BigNum2 instead of BigNum (And change the ranges....)

I'd suggest...

BigStr

instead.

 

[Juan Pablo González]

I'd suggest...

BigStr

instead.

ok ok, now we're discussing semantics in formulas !

 

[chaos]

Re: need formula to retrieve word value from last cell in co

Hey guys,

I just noticed the additional postings. Thanks for the help.

Paddy, I see what you are saying about my tortured application of the Index formula. Everyday, I'm learning something new.

Regards,

chaos