CODEWITHSUNDEEP
excelexcel-formula
Swiftslide
Swiftslide

Reputation: 1347

Excel formula to get last entry in a row

I have a sheet where each row begins with a string identifier, followed by a series of values for each week since the sheet was created:

XXX    15    20    25
YYY    11    15    19
ZZZ    18    22    22

On another sheet I would like to have some of the string identifiers from the first page (not all), and only the latest value for that item:

XXX    25
ZZZ    22

I actually want multiple sheets of the second kind, with each string id possible appearing on more than one sheet.

I can't use VBA as this has to be viewed (and values added and altered) on my Windows Phone 7 (I do not believe the mobile version of excel supports VBA, but correct me if I'm wrong). Is there a formula that's able to do this?

Upvotes: 0

Views: 29703

Answers (1)

Alex Gordon
Alex Gordon

Reputation: 60882

The value of the last non-blank cell in ROW range

=LOOKUP(2,1/(A1:M1<>""),A1:M1)
  1. on your master sheet you should designate a hidden column like ZZ to contain the formula: =LOOKUP(2,1/(A1:ZY1<>""),A1:ZY1) this will put in column ZZ all the LAST values of that specific row
  2. on all of the other sheets that depend on your master, you can use a vlookup to retrieve the information in ZZ from the master sheet

Upvotes: 3

Related Questions