OFFSET Function in Excel

[Tanya]

Good Day,

Here is the layout of my spreadsheet:

Header Row:
Firstname,LastName,FirstInitial,Login,KeyFob,Account,Passw
ord,Administrator

FirstName = text
LastName = text
FirstInitial = left(FirstName)
Login = FirstInitial + LastName
KeyFob = text
Account = text
Password = text
Administrator = text

Here is sample data:
(Say this was what was contained in row B2

Bob, Harley, B, BHarley, 111, (e-mail address removed), 123abc, Sam
Hagar

Now there is a change that B3 (or subsequent further
rows) could look like this

blank, blank, blank, blank, 111, blank, blank, blank

==========================================================

Header Row (A1 Row)
Firstname,LastName,FirstInitial,Login,KeyFob,Account,Passw
ord,Administrator
A2 Row
Bob, Harley, B, BHarley, 111, (e-mail address removed), 123abc, Sam
Hagar
A3 Row
blank, blank, blank, blank, 111, blank, blank, blank
==========================================================
I was using

=OFFSET($A$1,COUNTA(A:A)-1,0)

to return the last FirstName, AND

=OFFSET($A$1,COUNTA(A:A)-1,1) to get you last LastName,
etc, etc, etc for remaining cells.

(I need to record the last record entered in a different
area on the spreadsheet)

Now that I have a record where there are blanks, my
formula is not working.

What formula can I use to get the last records entered on
my spreadsheet?

Help!!

 

[Frank Kabel]

Hi
for the last text value use:
=LOOKUP(REPT("z",255),A:A)

for the last numeric value use:
=LOOKUP(9.99999999999999E307,A:A)

if you don't know which type of data you have use:
=LOOKUP(2,1/(A1:A65535<>""),A1:A65535)

 

[Tanya]

Hi Frank,

I am not sure what you mean by this.
Do I use the first formula to get the last record I have
entered? (i.e. FirstName, Lastname?)

 

[Frank Kabel]

Hi
yes if you're looking for the last text value use the first formula.
For more about this see:
http://www.xldynamic.com/source/xld.LastValue.html