How to create a range relative to current cell in google spreadsheet

Question

I have put the same question/requirement again but with a clear and small example to convey the intention correctly.

I have one column with dates, but not every row necessarily has it. The adjacent column should have the number of days occurred since that date. What should be the formula for that. I have tried several things, but still cannot get it correct. Below is an example.

       A           B         C              D
1      S.no.     Task        Date        Number of days
2       1        task1       10may16        6
3       1.1      task2                      6
4       1.2      task3                      6
5       2        task4       12may16        4
6       2.1      task5                      4
7       3        task6       13may16        3

Here how to get the correct values in column 'D', especially in D3, D4 and D6? For these cells, it takes the reference date from previous filled row of column 'B'. The current date is assumed to be 16th May 2016.

old question below

I am trying to find out a non-empty cell in a column based on another cell in a different column. For example, which is the last non-empty cell in column B1:Bx, where 'x' is the row number in column E where I use this value, say 24, when I am trying to use that value in E24.

In my case the column B contains dates and 'E' contains the difference between current date and the date in column 'B'. Sometimes the corresponding column doesn't have a date, in which case, I would go up to find last date in that column and find difference.

I have been looking up internet to find out ways to do this but hasn't found anything yet. One way that might be close to what I want is given at Get the last non-empty cell in a column in Google Sheets. But this also doesn't work for me. The difference is that I don't search complete column but only till the row where the value is required in 'E' column. This is where I am not able to adapt the given formula. The one that I tried, given below, results in #Error!.

=INDEX(FILTER( "B2:B"&ROW()-1 , NOT( ISBLANK( "B2:B"&ROW()-1 ) ) ) , ROWS( FILTER( "B1:B"ROW()-1 , NOT( ISBLANK( "B2:B"&ROW()-1) ) ) ) )

So, I guess, I want to know the correct way of creating range based on current row value. The error most likely is in part "B2:B"&ROW()-1

Max Makhrov Max Makhrov · Accepted Answer · 2016-05-13T06:41:09

The simplest way is to use exrta column with dates

 A        B        C             D             E
S.no.   Task    Date         Date        Number of days
1       Task1   5/10/2016    5/10/2016                6
1.1     Task2                5/10/2016                6
1.2     Task3                5/10/2016                6
2       Task4   5/12/2016    5/12/2016                4
2.1     Task5                5/12/2016                4
3       Task6   5/13/2016    5/13/2016                3

formula for D2:

=if(C2="",D1,C2)

for E2:

=TODAY()-D2

need to copy formulas down.

use your formula with indirect:

=INDEX(FILTER( indirect("B2:B"&ROW()-1) , NOT( ISBLANK( indirect("B2:B"&ROW()-1) ) ) ) , ROWS( FILTER( indirect("B2:B"&ROW()-1)  , NOT( ISBLANK( indirect("B2:B"&ROW()-1)) ) ) ) )

How to create a range relative to current cell in google spreadsheet

old question below

1 Answers