CODEWITHSUNDEEP
excelexcel-formulagoogle-sheets
osherez
osherez

Reputation: 99

Stuck With A Pull data Formula

Im banging my head on the wall for the past 3 hours trying to figure out How can solve this issue.

Iv managed to make a formula that pulls specific data that iv specified. in my ex:

All the people who has 2 days left and are in "On hold" status (see table for reference).

=QUERY(data,"select D where A contains 'on hold' and E<=2",0)

1.the issue is that iv managed to create that formula only by creating a cell (E) that subtracts the day difference on the dates entered (B:C).

2.how can create a subtract formula inside a query formula. please see link for detailed table.

3.Second issue is that my table is dynamic and grows with data over time. how can I make the formula to pull data from the updating cells. ( iv crated a name range for the table called "data" and that ranged is inside the formula)

thanks in advance to all appreciate the help :)

sheet

Upvotes: 1

Views: 58

Answers (1)

Karl_S
Karl_S

Reputation: 3564

Named ranges have a hard defined end row. If you can use a range such as D5:F instead, you will not need to change the named range. This range will start at cell D5 and include columns D, E, and F until the last row in the sheet.

The query needs to use datediff:

=QUERY(A2:E,"select D where A contains 'on hold' and datediff(C, B) <= 2",0)

See the Scalar Functions portion of the Query reference for more.

Upvotes: 1

Related Questions