r/excel•
8BallRunOut

Offset number of cells from last text cell

solved

I have a pretty trivial problem that should be relatively easy to solve, but I just can't seem to get it right. I've inherited this workbook that has data laid out very weirdly. I have a row with both text data and numeric data and I'd like to return the left-most text cell above every numeric cell. For example, I have...

Book1 | 1 2 | Book2 | 3 4 5 6 7 | Book3 | 8 9 10 |

And I'd like to get "Book1" above the 1 and 2 cells, "Book2" above the 3,4,5,6, and 7 cells, and "Book3" above the 8,9, and 10 cells. *Note: the range here is always changing, new columns will be inserted regularly thus changing the numeric entries.

I'm open to a UDF as well since it's already macro-enabled.

Thanks in advance!

Posted Jun 21, 2017, 8:46 PM UTC

01
8BallRunOutOP26•

Well, I managed to solve it using a helper row with the following formula...

=IF(ISTEXT(B1),0,A2+1)

Where A2 =0 because a text cell will always be above it.

1