Return address of last cell

  • Thread starter Thread starter emm8080
  • Start date Start date
E

emm8080

Hi

I want to search a range of cells in a column to find the last use
cell and then return the address of the last cell so that I can us
this address in another function. The address of the last cell wil
change on a weekly basis.

This is the function I am using to find a unique value in a colum
using a condition from another column. (kindly provided by Fran
Kabel!)

=SUM(IF(FREQUENCY(IF(($E$2:$E22=$A27),MATCH($D$2:$D22,$D$2:$D22,0),""),IF(($E$2:$E22=$A27),MATCH($D$2:$D22,$D$2:$D22,0),""))>0,1))

I need the E22 and the D22 (ie the last cell addresses in thei
respective columns) to be updated when the new function returns th
last cell address of each column (unless this can be done within th
current function??). The two columns will always have the same numbe
of used rows ie if column E's last cell address is 22 then column D'
will also be 22.

Thanks

Emm
 
Assuming that you have text values in the range of interest in column D...
Try:

X2:

=MATCH(REPT("z",255),D:D)

Change the formula...

=SUM(IF(FREQUENCY(IF(($E$2:$E22=$A27),MATCH($D$2:$D22,$D$2:$D22,0),""),IF(($
E$2:$E22=$A27),MATCH($D$2:$D22,$D$2:$D22,0),""))>0,1))

to:

=SUM(IF(FREQUENCY(IF(($E$2:INDEX(E:E,X2)=$A27),MATCH($D$2:INDEX(D:D,X2),$D$2
:INDEX(D:D,X2),0),""),IF(($E$2:INDEX(E:E,X2)=$A27),MATCH($D$2:INDEX(D:D,X2),
$D$2:INDEX(D:D,X2),0),""))>0,1))
 
Thanks Aladin - just what I wanted!

I have posted a new thread re adding values in a column after locatin
the last cell - I am sure it is similar to above, but I couldn't wor
it out!

Emm
 
That needs something a bit different...

=SUM(A2:INDEX(A:A,MATCH(9.99999999999999E+307,A:A))

but

=SUM(A:A)

would equally work if A1 does not house a number you want to exclude from
the sum.
 
Back
Top