ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Return Each Last Non-blank Cell, Value 2 Column Offset, Many GAPS (https://www.excelbanter.com/excel-worksheet-functions/270896-return-each-last-non-blank-cell-value-2-column-offset-many-gaps.html)

 Jim[_14_] August 18th 11 04:38 PM

Return Each Last Non-blank Cell, Value 2 Column Offset, Many GAPS

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.

 isabelle August 18th 11 06:37 PM

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.

 All times are GMT +1. The time now is 07:28 AM.