• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Look Up by past dates

cmf735

New Member
Hi so I'm trying to pull a value into a spreadsheet based on the date being older than today. I've attached the top of the table and the first row below it. So what I'm trying to do is return the JO# column as long as the Production Due date is older than today. I need to pull all late orders into it's own sheet based off data that I refresh daily. So what I'm trying to do without success is =LOOKUP(Table_owssvr[@[JO'#]], IF(Table_owssvr[@[Production Due]],"<"&TODAY())). I'm hoping someone will be able to help me with this. Thanks in advance!
JO#SO#CustomerExpediteDock DateKanban OrderCurrent StatusRequestedProduction DueShip DatePart NumberRevQTYNotesOwnerSpecial InstructionsReceipt InfoItem TypePath
TS-0006841000514N/A
FALSE​
12/25/2026​
FALSE​
In-Process Cell HV
12/25/2026​
1/13/2020​
1/14/2020​
TS-520
3​
C101134-LE2ItemLists/JO List
 
Last edited by a moderator:

Peter Bartholomew

Well-Known Member
Several strange things going on here. Firstly you are using a lookup to search a single cell for a match. Then the parameters of the IF function look more like they are set up for SUMIFS/COUNTIFS. I suspect the ideal solution with Excel 365 would be to use FILTER but, starting where we are
Code:
= LOOKUP(Table_owssvr[@[JO'#]],
    IF(Table_owssvr[@[Production Due]]<TODAY(),
    Table_owssvr[@[JO'#]]))
would look for the first cell and return it if the IF returns it, error otherwise. Slightly more standard might be
Code:
= LOOKUP(TRUE,
      Table_owssvr[Production Due]<TODAY(),
      Table_owssvr[JO'#])
to search for the first occurrence of a JO# that meets the condition.
 

cmf735

New Member
Hi Peter,

It doesn't have to be a LOOKUP. That's what I was trying to do because I don't want it to pull in today's orders and future orders. With an IF formula I need a true and false and all of the false will be future orders. I was trying to avoid that because there are other things I'm looking to do with this spreadsheet and those false values will complicate other formulas.
 

Peter Bartholomew

Well-Known Member
If you don't want a helper column, then the condition
([Production Due]<TODAY())
has to find its way into the other formulas you are developing. If they are SUMIFS or COUNTIFS the condition breaks into two parts as you did in your initial formula e.g.
= COUNTIFS([Production Due], "<"&TODAY())
 
Last edited by a moderator:

cmf735

New Member
I was able to get it to pull over with =IF(Table_owssvr[@[Production Due]]<TODAY(), Table_owssvr[@[JO'#]], "FAIL") but now it's creating the complication of trying to sum total but ignoring anything with a "FAIL". I'm trying to do a SUMIF for LATE TOTAL counting the WO TOTAL but ignoring anthing with "FAIL". I tried =SUMIFS(U:U,A:A,NOT("FAIL")) but all I'm getting is a 0.
JO#SO#CustomerExpediteDock DateKanban OrderCurrent StatusRequestedProduction DueShip DatePart NumberColumn1QTYNotesSpecial InstructionsReceipt InfoKittingAssemblyQCPackagingWO TotalLate Total
MM-005228-D1006638ANACOR
FALSE​
12/25/2026​
FALSE​
INV To Be Kit
12/25/2026​
4/22/2021​
#########​
MM-657
0​
1​
0​
0​
0​
0.08​
0.25​
0.08​
0.08​
0.49​
MM-005228-E1006638ANACOR
FALSE​
12/25/2026​
FALSE​
INV To Be Kit
12/25/2026​
4/22/2021​
#########​
MM-657
0​
1​
0​
0​
0​
0.08​
0.25​
0.08​
0.08​
0.49​
FAIL
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
0.08​
0.25​
0.25​
0.17​
0.75​
FAIL
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
0.08​
0.25​
0.25​
0.17​
0.75​
 
Last edited by a moderator:

Peter Bartholomew

Well-Known Member
I used your formula to define the content of an additional column "CurrentJO".
The number of 'current jobs' was correctly given by
= COUNTIFS( Table_owssvr[CurrentJO],"<>FAIL")
and the part count would be
=SUMIFS(Table_owssvr[QTY], Table_owssvr[CurrentJO],"<>FAIL")

I don't use entire columns, except when I have exactly 1048575 rows, hence the table. That is just personal choice though.
 
Last edited by a moderator:

cmf735

New Member
The reason why I was using the full column is because the data can change from day to day but I'm trying to count the WO total while ignoring everything at the bottom of the table that has a FAIL JO#.
 

Peter Bartholomew

Well-Known Member
That is exactly what Excel Tables were introduced for in 2007. The structured references adjust automatically whenever data is added to the table.
BTW Sorry I missed that Fail appears with the JO rather than in its own column.
 
Top