0

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?

Swiftslide
  • 1,307
  • 7
  • 23
  • 34

1 Answers1

3

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
Alex Gordon
  • 57,446
  • 287
  • 670
  • 1,062