Forum Discussion
Solved
Indexing or Referencing the next cell above with value
Hi,
I'm wondering if there's a function or method to reference the first cell up in a column that has a value with an unknown number of rows? I can manually do it with using INDIRECT or OFFSET but this requires nesting multiple IF functions to address the unknown number of rows. Table below is a simplified example of what I'm trying to accomplish.
Column B has values with seemingly random row spacing between. Column A needs to reference the value in Column B's "section". The Column B variables could be alphanumeric.
| =FORMULATEXT(Column_A) | Column_A | Column_B |
| =[@[Column B]] | 1.0 | 1.0 |
| =INDIRECT(ADDRESS(ROW()-1,COLUMN()+1)) | 1.0 | |
| =[@[Column B]] | 2.1.1 | 2.1.1 |
| =INDIRECT(ADDRESS(ROW()-1,COLUMN()+1)) | 2.1.1 | |
| =INDIRECT(ADDRESS(ROW()-2,COLUMN()+1)) | 2.1.1 | |
| =[@[Column B]] | 3.1 | 3.1 |
| =INDIRECT(ADDRESS(ROW()-1,COLUMN()+1)) | 3.1 | |
| =INDIRECT(ADDRESS(ROW()-2,COLUMN()+1)) | 3.1 | |
| =INDIRECT(ADDRESS(ROW()-3,COLUMN()+1)) | 3.1 | |
| =[@[Column B]] | 4.3.2 | 4.3.2 |
| =INDIRECT(ADDRESS(ROW()-1,COLUMN()+1)) | 4.3.2 | |
| =INDIRECT(ADDRESS(ROW()-2,COLUMN()+1)) | 4.3.2 | |
| =INDIRECT(ADDRESS(ROW()-3,COLUMN()+1)) | 4.3.2 | |
| =INDIRECT(ADDRESS(ROW()-4,COLUMN()+1)) | 4.3.2 | |
| =INDIRECT(ADDRESS(ROW()-5,COLUMN()+1)) | 4.3.2 | |
| =INDIRECT(ADDRESS(ROW()-6,COLUMN()+1)) | 4.3.2 | |
| =[@[Column B]] | 5.0 | 5.0 |
| =INDIRECT(ADDRESS(ROW()-1,COLUMN()+1)) | 5.0 |
Thanks!
Guan
2 Replies
- That worked perfectly! Thank you!!
