Copying sheet row when date has been reached

[saileen]

Hi
I am relatively newer to excel. I have an inventory log I am trying to update for when items are needing to be replaced due to expiry or low order level. I am looking to have items that are expiring when date is = < 3months to have copied onto another sheet tab for ease of creating a new purchase order.

Red	Product is Expired/Missing
Orange	Product will epxire in less than 30 days
Yellow	Product will expire in less than 90 days
Light Green	Product will expire in more than 90 days
Dark Green	Product will expire in more than 180 days

I have 4 different columns with expiration dates because different order batches have different expiration dates. They are in columns J, L, N & P.

Is it possible for have a portion of the row transfer over to another sheet tab? Specifically for me, columns B, C, H, J, L, N & P

I'm also wondering if I could get help tweaking my conditional formatting formula for my expiration dates to change colour when they reach the dates above for a visual.. but it turns blank cells the colour when i prefer them to be plain if there is nothing inside of them
for yellow example
=AND(NOT(ISBLANK(J:J,L:L,N:N,P:P)), < TODAY() +90 )

 

[saileen]

 

[Asbestos_Jen]

I think that the copying to a new sheet will require a VBA solution, which I'm not really equipped to help you with. I can offer help with your Conditional formatting issue, though. You have to make a separate rule for each range; the way you have nested the ISBLANK won't work. I've attached a sample with XLBB, but my conditional formats don't show up with it on here, so I've included the formulas as separate cells (column L). I also included a CF for if the date is not valid, as they defaulted to dark green (30-Feb-24 is invalid).

2023-11-30_2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2							DATE FORMAT	YYYY/MM/DD
3								Product is expired/missing
4								Product will expire in less than 30 days
5					Invalid date			Product will expire in less than 90 days
6								Product will expire in more than 90 days
7								Product will expire in more than 180 days
8		ColumnB	ColumnC	ColumnD	ColumnE	ColumnF	QTY Total	Missing	QTY 1	Expiration 1	QTY 2	Expiration 2	QTY 3	Expiration 3	QTY 4	Expiration 4
9
10							2	2	2	2024/01/30
11							4	2	4	2024/03/30
12							0	0
13							3	0	3	2024/09/30
14							3	0	3	2024/01/30
15							3	0	3	2024/08/30
16							1	0	1	2024-02-30
17							3	0	3	2024/04/30
18							1	0	1	2023/12/30
19							2	0	2	2023/10/30
20							3	0	3	2025/01/30
21							5	1	2	2023/09/30	3	2024-02-30
22							3	0	3	2024/04/30
23							9	0	6	2023/10/30	3	2023/12/30
Sheet1

Cell Formulas
Range	Formula
G10:G23	G10	=SUM(I10,K10,M10,O10)

 

[saileen]

I think that the copying to a new sheet will require a VBA solution, which I'm not really equipped to help you with. I can offer help with your Conditional formatting issue, though. You have to make a separate rule for each range; the way you have nested the ISBLANK won't work. I've attached a sample with XLBB, but my conditional formats don't show up with it on here, so I've included the formulas as separate cells (column L). I also included a CF for if the date is not valid, as they defaulted to dark green (30-Feb-24 is invalid).

Thank you for this!
Oops, just noticed this too - guess I was trying to stretch February out lol