Incorporating Filters within a column into a formula

[PrettySophia]

Hello,

I was wondering how I would go about putting together 2 separate formulas for the case below:

I have table, within the table I have a column named " Status" and "Date of request".

Within the "Status" column, I have two filters: "Waiting on dealer" and "part is locked"

I would like to create a formula that captures all the entries within the filter "Waiting on dealer" and automatically shows if the entry exceeded exactly 7-12 days from the column "Date of request"


The same goes for the second formula : a formula that captures all the entries within the filter "Part is locked" and automatically shows if the entry exceeded exactly 12-30 days from the column "Date of request"



If this seems unclear or complicated, please let me know what information you need and I will get back to you ASAP!!

I a putting together a very important spreadsheet for work and I would really appreciate if anyone can help me on this issue. I have spent hours trying to figure it out

PLEASE HELP!!!


thank you all

 

[Chrisdontm]

So you have a column that has a date................................= Date of Request ( Column " A " )
and a column that has the status of those requests called....= Status ( Column " B " );
( Or is the status column split into two columns ? )
and in that column of "Status" it declares, perhaps with a drop down menu;
" Waiting on Dealer " or " Part is Locked "
Then you want to list elsewhere all the " entries " for each of those...
Are the entries in their own columns...?

If you declare what columns each of these things are in, ( A, B, or C, or etc...) then a formula could be formed...
And where do you want the list put...?
I suppose two lists will be needed...
If in different tabs, then state the names of each tab...
Cell references are needed to make formulas work...
What you ask, can be done...

 

[PrettySophia]

So you have a column that has a date................................= Date of Request ( Column " A " )
and a column that has the status of those requests called....= Status ( Column " B " );
( Or is the status column split into two columns ? )
and in that column of "Status" it declares, perhaps with a drop down menu;
" Waiting on Dealer " or " Part is Locked "
Then you want to list elsewhere all the " entries " for each of those...
Are the entries in their own columns...?

If you declare what columns each of these things are in, ( A, B, or C, or etc...) then a formula could be formed...
And where do you want the list put...?
I suppose two lists will be needed...
If in different tabs, then state the names of each tab...
Cell references are needed to make formulas work...
What you ask, can be done...

Hello Chris,

thank you so much for the quick response!

The two columns are:

Date of Request is column (F)
Status is column (J) ( this is not split)

The " Entries" are their own rows is ID's within the columns..

I just need to know when the "Date of request(F) for "Locked parts"(J) exceeds 12-25 days from TODAYS (A) date.
As well as : When the "Date of request (F) for "waiting on dealer" (J) exceeds 7-14 days from TODAYS (A) date.

Columns A, B ,C I left open to display the formulas rules

( There are two blank columns (B,C) next to TODAY column.
( The ID column ( D) is a little off center)



[TABLE="width: 1440"]
<tbody>[TR]
[TD]TODAY[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12/9/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD] ID[/TD]
[TD]Tracking Number[/TD]
[TD]Date of Request[/TD]
[TD]VIN Number[/TD]
[TD]Requestor[/TD]
[TD]Part[/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD="align: right"]12/9/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3903[/TD]
[TD][/TD]
[TD="align: right"]9/1/2015[/TD]
[TD] 0K72059[/TD]
[TD]Breakdown[/TD]
[TD]Rear light in the side panel, left[/TD]
[TD]part is locked[/TD]
[/TR]
[TR]
[TD="align: right"]12/9/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4060[/TD]
[TD][/TD]
[TD="align: right"]9/9/2015[/TD]
[TD] T970116[/TD]
[TD]TC[/TD]
[TD]Turbocharger[/TD]
[TD]Waiting on Dealer[/TD]
[/TR]
[TR]
[TD="align: right"]12/9/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4488[/TD]
[TD][/TD]
[TD="align: right"]9/28/2015[/TD]
[TD] D153023[/TD]
[TD]Breakdown[/TD]
[TD]Oil Spraying Nozzle[/TD]
[TD]Part is locked[/TD]
[/TR]
[TR]
[TD="align: right"]12/9/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4490[/TD]
[TD][/TD]
[TD="align: right"]9/28/2015[/TD]
[TD] D153023[/TD]
[TD]Breakdown[/TD]
[TD]Actuator[/TD]
[TD]Part is locked[/TD]
[/TR]
[TR]
[TD="align: right"]12/9/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4942[/TD]
[TD][/TD]
[TD="align: right"] 10/21/2015[/TD]
[TD] VV92808[/TD]
[TD]Breakdown[/TD]
[TD]Valve collet[/TD]
[TD]Part is locked[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Chrisdontm]

Is this what you are looking for...?

https://www.dropbox.com/s/a6cbcmkjlsam6qc/PA-help.xlsx?dl=0

 

[PrettySophia]

Is this what you are looking for...?

https://www.dropbox.com/s/a6cbcmkjlsam6qc/PA-help.xlsx?dl=0

I'm Sorry I cannot open the link:/

 

[Chrisdontm]

I don't understand why not, but, put this information in each cell;

B2 Is "Waiting on Dealer" between 7 & 14 days from today?

B3 IF($J3="","",IF($J3="Waiting on Dealer",IF(AND($A3>$F3+7,$A3<=$F3+14),"Yes","No"),""))

Then copy B3 down the column...

C2 Is "Locked Parts" between 12 & 25 days from today?

C3 IF($J3="","",IF($J3="part is locked",IF(AND($A3>$F3+12,$A3<=$F3+25),"Yes","No"),""))

Then copy C3 down the column...

I believe this is what you are wanting, if it is close, we can tweak it as needed...

{ B2 & C2 are just labels for the columns, there are no functions within them...}