2

I have a spreadsheet which resembles the below. Column A is my data and Column B is what I am trying to achieve

         A           B
   +-----------+-----------+
1  | Some text |   A1:A3   |
   +-----------+-----------+
2  |           |           |
   +-----------+-----------+
3  |           |           |
   +-----------+-----------+
4  | Some text |   A4:A8   |
   +-----------+-----------+
5  |           |           |
   +-----------+-----------+
6  |           |           |
   +-----------+-----------+
7  |           |           |
   +-----------+-----------+
8  |           |           |
   +-----------+-----------+
9  | Some text |   A9:A9   |
   +-----------+-----------+
10 | Some text |  A10:A10  |
   +-----------+-----------+
11 |           |           |
   +-----------+-----------+
12 |           |           |
   +-----------+-----------+

As you can see, Column A just contains empty and non-empty cells (all cells, including empty ones, actually contain a formula). In column B, I need to return a range starting at the current row and ending at the first non-empty row, but only if Column A of the current row is not blank. The returned result is to be used within another function. For example B1 could be COUNTBLANK(A1:A3), although the actual function is not COUNTBLANK, but a custom function

Is this possible?

Thanks

Typhoon101
  • 2,063
  • 8
  • 32
  • 49

1 Answers1

2

Please add a letter at the bottom of your "occupied" ColumnA then try, in B1 and copied down: 

=IF(A1="","","A"&ROW()&":A"&ROW()+MATCH("*",A2:A100,0)-1)

Alternative to account for restriction mentioned by OP 

=IFERROR(IF(A1="","","A"&ROW()&":A"&ROW()+MATCH("*",A2:A$1048576,0)-1),"A"&ROW()&":A"&ROW())
pnuts
  • 58,317
  • 11
  • 87
  • 139
  • Thanks pnuts. However, i cannot add anything in Column A. It's content is dynamically driven. I have no idea how many rows will be occupied from one minute to the next – Typhoon101 Sep 24 '14 at 15:19
  • 1
    Thanks again pnuts. I appreciate your efforts. This isn't actually working correctly though. As i mentioned earlier, no cells in column A are completely blank. They all contain a formula. The blank cells are just those where the formula evaluates to null – Typhoon101 Sep 24 '14 at 15:40
  • Yes, helper columns are fine – Typhoon101 Sep 24 '14 at 15:49
  • No worries. I have just tried FALSE, and that had no effect. – Typhoon101 Sep 24 '14 at 15:58
  • 1
    I am using Excel 2010. If you look at my cell arrangement in my original question, with formulas in all the A cells, B1 contains "A1:A1", B4 contains "A4:A4" etc. If I physically delete the formulas from the empty cells in the A Column, the B column shows the correct values. – Typhoon101 Sep 24 '14 at 21:56
  • Thanks again pnuts. i really do appreciate you persisting with this. We are one step closer, but ISFORMULA was introduced in Excel 2013. i am running Excel 2010. is there an equivalent? – Typhoon101 Sep 25 '14 at 08:41