Offset number of cells from last text cell
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
Well, I managed to solve it using a helper row with the following formula...
Where A2 =0 because a text cell will always be above it.