View Single Post
  #2   Report Post  
Old August 18th 11, 06:37 PM posted to microsoft.public.excel.worksheet.functions
isabelle isabelle is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2011
Posts: 587
Default Return Each Last Non-blank Cell, Value 2 Column Offset, ManyGAPS

hi,

array formula to validate with "ctrl + shift + enter"

=INDEX(C:C,MAX(IF(A3:C100<"",ROW(A3:C100))),1)

--
isabelle



Le 2011-08-18 11:38, Jim a écrit :
Looking for a formula that will look down a column of cells that has
many blanks gapped in-between other cells containing text, numbers,
etc and for each block of cells with continuous data, find the last
non-blank cell and return a value that is 2 columns away in the same
row as the last non-blank cell. To clarify, there may be data that
extends down thousands of cells and the blocks of non-blank data in
the applicable column may be as few as 1 continuous or as many as 100
continuous pieces of data and I am only wanting to pull the source
cell that is adjacent to the last data populated cell in each block,
but for all blocks down the spreadsheet.