Using If Function to make an update to a Reference Sheet.

Doyle54

Thank you at start!

Field Officers (FO) are inspecting Well Pads for issues. There are 344 pads for them to visit per quarter. They inspect a pad and input the results of their pad inspection into Sheet1 row. Somewhere between initial going on pad and finishing loading data in Pad Inspection Form, I want SS to look for the same well pad "Location" in Sheet1 as in Sheet2. Sheet1 is for loading new inspections data and Sheet2 has the 344 Pad records to be inspected quarterly.

Execution - =if(Sheet1:location1=Sheet2:location@cell, Sheet2:Found@cell=Sheet2:Found+1,)

Question: Is my equation about right? There could be multiple well pads with the same general location (like Section, Township, Range - 21-149-92. Will my equation update all of the pads with the same general location? Will the found be updated everytime an addition is made to Sheet1? Should I be more specific and Concatenate the Pad name -> first 4 letters with the location ID in Sheet1 and Sheet2 and use that as the search variable?

Ultimately – I just want the FOs to complete their Quarterly 344 inspections and I need to track this and push if not all 344 have been inspected. I may want to create a Done column in Sheet 1 so that the Found cell via Location@row is added to the new record. Not critical but interesting statistic.

Once the FO finishes loading information on their pad inspection into the Sheet1@row, the Sheet1@row will be automatically moved (via Automation) to PadInspArchive Sheet.

Doyle54

I think I have a solution - by Adding a new record to my 344 Record Sheet 1 set vs a Reference Sheet 2. Using the Location Column in the new record in Sheet 1 - to search in the remainder location cells in Sheet 1 via a range statement. Then if found, Check x in the Found cell which will automatically move the row to the Archive Sheet and Found2 (Column for checking for multiple visits to the same pad) is updated - Found2=Found2+1. If not found, nothing happens except in my report I will search for a Found2 cell in the Sheet that has 0 (zero) founds.

Question - Challenge - I think I know how to use Automation to move any of the rows in the Sheet 1 that have 0 Founds to the Archive Sheet. But how do I reset the 344 records' Found Column to 0 (zero) at the start of each quarter?

c

RossL

@Doyle54

I am thinking you could use a Metric and intake sheet set up. The intake sheet would be were inspectors fill out a form with their findings. The metric sheet would use the unique id of each pad to check to see if the inspection was done between the dates set. Each quarter could have it's own parent row on the Metrics sheet with the children containing all 344 ID numbers. Use the INDEX/MATCH combination to check to see if has been done and pull data out of the Intake sheet. this way you can keep track over the year how many are done and how many may have been missed. This way field inspectors only need to fill out a form and not have to go into a sheet and find a row to manually fill anything out.

Doyle54

Let me study your concept. The idea is new to me. Thanks.

Doyle54

RossL – Great idea but I still have to follow a sequence with each record

1 Field Officers prefer to use form to input new record which is fine. They input a new inspection into a form which captures that data in a sheet in various ways. But let's use your scenario of a separate sheet. Sh1. 

2 The Parent – Child Sheet, Sh2, has to execute a search via Index or Vlookup – I have used both. The LocationSh1@cell in Sh1 is compared with the LocationSh2@cell:

           If it finds a match, the FoundSh2@row is updated by + 1 and FoundSh1 is updated to = 1

           If it does not find a match, neither  FoundSh# is changed

3 Via automation the @row in Sh1 is moved to Archive, the Master list.

My question is – I have tried writing the Step 2 Code Multiple Ways as well as have reached out to multiple sources. I have even gone back to the IT department of the oil company that recommended Smartsheet to begin with and offered them a Bottle of Fine Wine if they can help me with the single line of code. Nothing yet.

The idea behind the FoundSh2 and +1 is that we want the Officers to finish their 344 inspections before going back to a pad. So, we need a way to monitor how many times they visit a pad in a quarter and let them know if they are repeating themselves before other pad are inspected.

I cannot offer an unknown party a bottle of wine but I can offer to send a check to your favorite charity.

Check my "Simple If" Posting in Community.

Sincerely,

c

RossL

@Doyle54

I think you could have the Metrics look something along these lines. this is taking a count of all the pads listed in the intake sheet.

the intake would look like this

the formula for the quarter designation would be

=(IF(AND(MONTH([Inspection Date]1) >= 1, MONTH([Inspection Date]1) < 4), "Q1", (IF(AND(MONTH([Inspection Date]1) >= 4, MONTH([Inspection Date]1) < 8), "Q2", (IF(AND(MONTH([Inspection Date]1) >= 8, MONTH([Inspection Date]1) < 11), "Q3", "Q4"))))))

you would need a list of all the pads to put in the Metrics sheet for each quarter. you could add more info into the intake like pass or fail and add counts to the metrics that countifs Q1 Pass and Fail ect.

Doyle54

Let me look at your proposed coding. Do you have a favorite charity?

Doyle54

I can handle the Parent Children Sheet, Sheet2 I think in my example. I think I can even use just one list of the 344 and adjust the Quarter - Year aspect in a more condensed manner. Have you any ideas as to how to get the Found and Not Found tracking done?

RossL

if this works for you and you would like to donate to: https://www.rescuehorses.org/ that would be great but don't feel like you need to.

Doyle54

Absolutely I would sincerely enjoy doing so. We have to deal with the updating the Found@cell for Sheet 1 and Sheet 2, though.

RossL

@Doyle54

I guess I don't fully understand the Found column. what is it doing? from the way it is described it sounds more like a COUNTIF function vs a VLOOKUP or INDEX function.