dropkickweasel
Board Regular
- Joined
- Feb 2, 2014
- Messages
- 70
I am working on a workbook to manage stock.
I have a master table with a list of products on it.
One of the columns on the list is an expiry date, called "Exp".
Another column is a location where products can be stored, called "Location".
Each location has its own sheet.
FILTER draws through only the products from the master sheet that are stored in that location.
SORT automatically lists them by expiry date.
The formula looks like this:
=SORT(FILTER(Master,Master[Location]="Fridge","No Records"),7,1)
Conditional formatting is applied to the expiry dates so I can tell when a product is out of date / going out of date soon.
The conditional formatting formulae follow this pattern:
=EOMONTH(G1,-1)<TODAY()
This highlights products which will be out of date by the end of the month, given that the expiry date is listed in column G.
This all works fine.
What I want to do is create an additional sheet of "Products Expiring Soon".
This sheet would have all products that are already out of date, or will be out of date by the end of the third month from now, in all locations.
Note: Products that are out of date are typically "Destroyed". This is listed as a location and has its own sheet like the other locations.
If I use a formula like:
=FILTER(Master,(Master[Exp]<TODAY())*(Master[Location]<>"Destroyed"),"No Results")
This works to give me a list of all products in all locations that haven't been destroyed and are out of date as of today.
However, I want to list all products that will be out of date by the end of the third month from today.
I've tried to modify my FILTER formula like this:
=FILTER(Master,(EOMONTH(Master[Exp],-3)<TODAY())*(Master[Location]<>"Destroyed"),"No Results")
But this results in a #VALUE error.
Is there any way to change this formula so it does what I want it to do?
Other things I've tried are:
Current improvisation:
I change "Today()" to "Today()+90", effectively moving today three months into the future.
But, I would like the worksheet to be as robust as possible as I will not be the end user, so having it specifically only list products that will be out of date by the end of the third month from today would be ideal.
Attempted improvisation:
I created an additional column on the Master table called "Expiring".
The formula in this column is:
=EOMONTH([@Exp],-3)
This takes the expiry date from the Exp column and pushes it to the end of the month three months prior. So a date of "01/03/2022" becomes "31/12/2021" for example.
Problem:
Changing the formula on my "Products Expiring Soon" sheet to:
=FILTER(Master,(Master[Expiring]<TODAY())*(Master[Location]<>"Destroyed"),"No Results")
Results in a #NUM error that I just don't understand.
The source for both the Exp column and the Expiring column are dates. The only difference is that the dates in the Exp column have been inputted directly and the dates in the Expiring column are the result of an EOMONTH formula.
Does anybody have any advice on how I might go about solving this?
The Master sheet is where new stock is added, so I'd like to keep that in chronological order.
Products marked as "Destroyed" on the master sheet have their row hidden so that they do not clog up the Master, but are still visible on the Destroyed sheet.
I have a master table with a list of products on it.
One of the columns on the list is an expiry date, called "Exp".
Another column is a location where products can be stored, called "Location".
Each location has its own sheet.
FILTER draws through only the products from the master sheet that are stored in that location.
SORT automatically lists them by expiry date.
The formula looks like this:
=SORT(FILTER(Master,Master[Location]="Fridge","No Records"),7,1)
Conditional formatting is applied to the expiry dates so I can tell when a product is out of date / going out of date soon.
The conditional formatting formulae follow this pattern:
=EOMONTH(G1,-1)<TODAY()
This highlights products which will be out of date by the end of the month, given that the expiry date is listed in column G.
This all works fine.
What I want to do is create an additional sheet of "Products Expiring Soon".
This sheet would have all products that are already out of date, or will be out of date by the end of the third month from now, in all locations.
Note: Products that are out of date are typically "Destroyed". This is listed as a location and has its own sheet like the other locations.
If I use a formula like:
=FILTER(Master,(Master[Exp]<TODAY())*(Master[Location]<>"Destroyed"),"No Results")
This works to give me a list of all products in all locations that haven't been destroyed and are out of date as of today.
However, I want to list all products that will be out of date by the end of the third month from today.
I've tried to modify my FILTER formula like this:
=FILTER(Master,(EOMONTH(Master[Exp],-3)<TODAY())*(Master[Location]<>"Destroyed"),"No Results")
But this results in a #VALUE error.
Is there any way to change this formula so it does what I want it to do?
Other things I've tried are:
Current improvisation:
I change "Today()" to "Today()+90", effectively moving today three months into the future.
But, I would like the worksheet to be as robust as possible as I will not be the end user, so having it specifically only list products that will be out of date by the end of the third month from today would be ideal.
Attempted improvisation:
I created an additional column on the Master table called "Expiring".
The formula in this column is:
=EOMONTH([@Exp],-3)
This takes the expiry date from the Exp column and pushes it to the end of the month three months prior. So a date of "01/03/2022" becomes "31/12/2021" for example.
Problem:
Changing the formula on my "Products Expiring Soon" sheet to:
=FILTER(Master,(Master[Expiring]<TODAY())*(Master[Location]<>"Destroyed"),"No Results")
Results in a #NUM error that I just don't understand.
The source for both the Exp column and the Expiring column are dates. The only difference is that the dates in the Exp column have been inputted directly and the dates in the Expiring column are the result of an EOMONTH formula.
Does anybody have any advice on how I might go about solving this?
The Master sheet is where new stock is added, so I'd like to keep that in chronological order.
Products marked as "Destroyed" on the master sheet have their row hidden so that they do not clog up the Master, but are still visible on the Destroyed sheet.
