Copying data from Tab1 over to 2 and 3 with individual conditional logic - if possible?

Occasional Contributor

Hey there - I know the general basics of excel and started to brainstorm a solution for a problem we have at work.

There is an excel sheet we enter Expiration dates in. If I set up a conditional logic to highlight dates that are greater than 30 days from todays date. Could I create two tabs in the same excel sheet and have each look at the main tab but have different conditional logic?

So basically

1 Excel sheet, 3 tabs

Tab 1 (Main excel sheet)

Tab 2 (Expiration dates <30 Daysaway)

Tab 3 (Expired)

 

Every time Tab 1 is updated/ another row is added I would like it to also copy over to 2 and 3 but with the designated conditional logic. Is this possible?

2 Replies

@Hongan_Huynh 

Yes (as far as I could understand the problem statement), it is possible to set up an Excel workbook with 3 tabs where the data from Tab 1 is automatically copied over to Tabs 2 and 3 with individual conditional logic. One way to achieve this is by using formulas in Tabs 2 and 3 that reference the data in Tab 1 and apply the desired conditional logic.

For example, in Tab 2 (Expiration dates <30 Days away),

you could use a formula like =IF(Tab1!A2-TODAY()<30,Tab1!A2,"") to check if the expiration date in cell A2 of Tab 1 is less than 30 days away from today’s date.

 If it is, the formula will return the expiration date from Tab 1. Otherwise, it will return an empty string. You can then use this formula for all the cells in Tab 2 where you want to display the expiration dates that are less than 30 days away.

 

Similarly, in Tab 3 (Expired), you could use a formula like =IF(Tab1!A2<TODAY(),Tab1!A2,"") to check if the expiration date in cell A2 of Tab 1 is before today’s date. If it is, the formula will return the expiration date from Tab 1. Otherwise, it will return an empty string. You can then use this formula for all the cells in Tab 3 where you want to display the expired dates.

This way, whenever you update or add new data to Tab 1, it will automatically be reflected in Tabs 2 and 3 based on the conditional logic you have set up.

@Hongan_Huynh 

Depends on your Excel version. You may use FILTER() or Power Query to generate data in Tab2 and Tab3