How to insert a dynamic cell address in a formula. For example I have data in intermittent cells in a row. I need to use "countblank" formula to count the number of blank cells after the last cell where there is data. I have used the formula LOOKUP(2,1/B:B<>""),ROW(B:B)) to find the last cell which has data. For example, this formula returns an answer 18 in column "B". I can use concatenate formula to get this address which is B18. To get this address (B18) in the countblank formula, what should I do?
1 Answer
Based on what I understand, you can use combination of CELL, OFFSET and INDIRECT and 2 Helper Cells to achieve what you want.
In below example sample data is in cells B:B. Last row in B is row 14.
Formula in helper cell E4 is
="B"&LOOKUP(2,1/(B:B<>""),ROW(B:B))This returns B14 which is the last filled cell reference in Col B.
In Helper Cell E5 put the following formula to get the reference of last row in Col B
="B"&ROWS(B:B)In my Excel 2013 this returns B1048576
Now formula in G4 is
=COUNTBLANK(INDIRECT(CELL("address",OFFSET(INDIRECT(CELL("Address",INDIRECT(E4))),1,0))):INDIRECT(E5))The part of the formula CELL("Address",INDIRECT(E4)) returns reference $B$14 and OFFSET adds 1 Row to it making it $B$15. COUNTBLANK uses these INDIRECT Cell references to get the expected Cell References and returns the expected result.
In below picture Cells E6 & E8 are just for reference. They are not used anywhere.
