Trigger notifications based on dates previously input

Jose da Silva

Hi all,

 

Trying to build 2 spreadsheets that need to communicate with each other at this level:

One of them (first) will have the raw material code, name, supplier, last date of testing, risk rating, test required, etc.

The other (second) spreadsheet will be a record of materials booked in.

Will it be possible that, by typing the raw material code and supplier on the second spreadsheet, it will tell me (by searching on the first) if a testing is necessary? By this i mean:

- Low risk (must be tested once per year for each supplier)

- High risk (every delivery must be tested)

 

Thanks in advance.

Andrée Starå

Hi Jose,

Yes, it sounds like it should work. Is the raw material code unique?

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Paul Newcome

It is very possible using cross sheet references. The specific setup would be dependent upon your details. Are you able to provide screenshots with sensitive/confidential data hidden or replaced with "dummy data"?

 

How would you want the result displayed? Are there any other options besides yearly and each delivery? 

Jose da Silva

Hi both,

 

Thank you for your replies.

Yes the raw materials have unique codes (ex.: MAT1234), but then the same code might have 2 or 3 different suppliers that need to be tested sepparatedly.

 

What is troubling me is the fact that i have quite a few variables.

1st - raw mat code

2nd - supplier

3rd - high/low risk

4th - last tested

5th - which test required

 

First screenshot is from the database with the required dates and all the suppliers of each material, row by row.

The second would be where we would type all the booked in raw materials and, by simply typing the code and supplier, should give us testing information on the last column...

Paul Newcome

The first step would be to create a helper column on both sheets that joins the Material Code and Supplier name to provide a unique value for each individual row.

 

=[Material Code]@row + Supplier@row

.

After that... What date are you using to determine when the next test should be done?

Jose da Silva

I kind of have that already. There is an individual code that differentiates all the materials, plus a suffix letter for each different supplier.

 

Ex.:

MAT1234 - ID: 75 (first supplier); 75A (second supplier); 75B (third supplier, etc...

 

Then the first criteria should be High/Low risk - if "High" - always requires test; if "Low" check last test date - will require test if was more than a year ago.

 

Then there is also a column that states the required test

Jose da Silva

Note that the ideal scenario would be to type just the Mat code (MAT1234) and the supplier....and by using those 2 specific references, get to the required material and data.

Paul Newcome

Ok. So the easiest way that I can think of to do this is as follows...

 

The Material ID and Supplier columns will be manual entry. You will want a helper column in this sheet that replicates the unique ID you have established in the Database sheet.

 

From there you would use a series of INDEX/MATCH functions to pull the regular data except for the column where it will display whether or not a test is needed.

 

=INDEX({Database Sheet First Column to Pull}, MATCH(Helper@row, {Database Sheet Unique ID Column}, 0))

=INDEX({Database Sheet Second Column to Pull}, MATCH(Helper@row, {Database Sheet Unique ID Column}, 0))

=INDEX({Database Sheet Third Column to Pull}, MATCH(Helper@row, {Database Sheet Unique ID Column}, 0))

 

So on and so forth...

.

To determine whether or not a test is needed, we will pull the Risk column from the Database Sheet in the same way, but we will put it in an IF statement to hit against the other requirements.

 

=IF(OR(INDEX({Database Sheet Risk Column}, MATCH(Helper@row, {Database Sheet Unique ID Column}, 0)) = "High", INDEX({Database Sheet Last Inspected Date Column}, MATCH(Helper@row, {Database Sheet Unique ID Column}, 0)) < DATE(YEAR(TODAY()) - 1, MONTH(TODAY()), DAY(TODAY()))), "y", "n")