Excel formula

[zac]

Hi,

Using Excel, I'd like Cell $A$2 to examine cells $A$3 to $A$n and
simply return the last value it finds - so if 'C' was written in
Column X and nothing was written in any subsequent columns the value
'C' would appear and so on.

How do I express this as a formuala?

Any help greatly appreciated,

Zac

 

[Cathy]

Hi,
I am not sure how to do this in a formula but you can put
the following in VB Code under the sheet change event.
Cathy



Dim rowcounter As Integer 'holds the number of rows
until a blank is encountered
Dim newval As string


Application.EnableEvents = False 'prevent the
worksheet_change event from starting
Application.ScreenUpdating = False 'prevent the screen
from flickering as changes are made

rowcounter = 3

Do While Range("A" & rowcounter).Value <> ""
rowcounter = rowcounter + 1
Loop

newval = Range(("A") & (rowcounter -1 )).Value
Range("A2").Value = newval

Application.EnableEvents = False 'prevent the
worksheet_change event from starting
Application.ScreenUpdating = False 'prevent the screen
from flickering as changes are made

 

[Alex Delamain]

clumsy but it will work!!

add a new row 1 and number the columns so b1 =1, c1=2 etc

then a2 = hlookup(max(if(b2:z2>0,b1:k1,"")),b1:k2,2) entered as a
array (ctrl+shift+enter

 

[mzehr]

Hi Zac,
You can try the following formula in $A$2 entered as an
array (Shift-Ctrl-Enter):
=LOOKUP(2,1/(A3:A10000<>""),A3:A10000)
It will return the last value in that range in column A.
The part about column x is a bit confusing. But hope this
helps.

 

[Alex Delamain]

Nice one mzehr - I knew there had to be a better way!

Zac
To modify this to count across columns rather than down a row use th
following

=LOOKUP(2,1/(B1:Z1<>""),B1:Z1)

again entered as an arra

 

[Sandy Mann]

Hi Zac,

I am slightly confused because you talk about a range $A$3:$A$n with is a
column but then ask about a 'C' in Column X which would suggest a row.
Assuming that you want a row and adapting Harlan's formula of 21 July 2004
in Row 3 try:

=IF(ISERROR(LOOKUP(2,1/(1-ISBLANK(B3:IV3)),B3:IV3)),"",LOOKUP(2,1/(1-ISBLANK
(B3:IV3)),B3:IV3))

HTH

Sandy

 

[mzehr]

Hi Alex,
Thanks. Actually credit goes to Aladin Akyurek/Frank
Kabel/Bob Umlas for pointing me this direction.

Mike

 

[laptop]

Hi everyone,

Thanks for all your replies.

I've got a working solution, thanks. I'll post it on the group tomorrow if I
have chance- and if anyone's still interested.
Essentially it uses a hidden worksheet, a list of numbers from 65 to 91, and
the MAXA, UPPER, CODE and CHAR functions. More later.

 

[zac]

A Working Solution:

There are two sheets. In this example they are named Sheet1 & Sheet2.

Sheet1 is the main Worksheet - the page on which the user enters data.

Sheet2 is what's going on behing the scenes. It can be hidden from the
user.

Sheet2 simply has an array of cells with a formula something like this
in each:

=IF(NOT(ISBLANK('Sheet1'!H10)),CODE(UPPER('Sheet1'!H10)),"")


This returns a value typically between 65 and 90 (the ASCII codes for
capital letters A-Z), or blank if the corresponding cell in Sheet1 is
blank.

Sheet1 then uses the following to achieve the intended result;

=CHAR(MAXA(Sheet2!1:1))

Likewise, this column can be protected from the user.

Thanks to my brother, Matt, for this solution.