Format the EARTC spreadsheet to enhance selection in one column based on another

[atdservicebot]

What application are you using?

Other / Not sure

Describe the problem.

I need assistance in formatting a sheet in SharePoint (https://cityofaustin.sharepoint.com/:x:/s/atdAMD/EXHg2qzlFqtDl2FokYexEDwBhwgQYHpw7vhQ6NjNPNmtEg?email=Erin.Benoit2%40austintexas.gov&e=4%3Avsgdtt&CID=9FED2156-C2FD-4B87-8044-6C04C0001BB8&wdLOR=cAE02FD10-0A44-4743-9AB5-3625E7AB2067).

I need for 1 column to require a selection based on what is entered in another column. This will likely be quite simple, I just don't remember how to do it. Thanks!

How soon do you need this?

Flexible — An extended timeline is OK

Requested By Erin B.

Request ID: DTS22-106025

[amenity]

@ChrispinP @KaroEngstrom - we didn't get to speak to this in Grooming today... But (assuming it's as simple as Erin indicated) can one of you set up a time with Erin to walk her through this? cc/ @dianamartin

[ChrispinP]

Reaching out to Erin.

[ChrispinP]

Set my initial estimation of work from talking to Erin. Timeline for being due is 3 weeks. Will do my initial review of how much work it will take next week.

[ChrispinP]

Have prepped formulas for the spreadsheet according to the rules set by Erin below:

Request: Column L- drop down [options: "Partial", "Full", and "Corridor (Full)"]

Based on that selection: Column L- "Partial" = Column M- (automatically enters:) "24ART21046 Detection Maintenance" Column N- **Select one: "220830_11469 Detection Maintenance North" -or- "220830 11473 Detection Maintenance South"

Column L- "Full" = Column M- (automatically enters:) "24ART81007 STPMM" Column N- **Select one: "220125 04365 Detection Installation North" -or- "220125 04372 Detection Installation South"

(If staff enters: column D= "Bond", Column L automatically= "Corridor (Full)") Column L- "Corridor (Full)" =
Column M- Select one: "24ART01071 Burnet Corr." , "24ART01072 Riverside Corr." , "24ART01073 Airport Corr." , "24ART01074 Lamar Corr." , "24ART01075 Guadalupe Corr." , "24ART01076 William Cannon Corr." , "24ART01077 Slaughter Corr." -or- "24ART01078 MLK Corr." Column N- Select one: (DOs not created yet)- nothing to select (can we come back and add these later, or I can add manually.

**Joshil says it should be able to select the North or South DO # based on the location entered in columns B & C. I don't know how that would work, so if not possible, I'm ok with requiring the selection of the North or South DO # by the user.

Will meet with Erin on Wednesday to show formulas and set a time and date for implementation.

[ChrispinP]

Showed the functionality to Erin & Joshil. They are happy to begin using right away.

Planned implementation for Thurs or Fri after business hours. Will provide support the following business day as necessary.

[ChrispinP]

Implemented the formulas early this morning and they are now live on the EARTC spreadsheet. I have informed Joshil and Erin.

Detection sheet Formulas for reference:

Column D - Bond =IFNA(VLOOKUP($A2, Signal_List!A:D, 4, FALSE), "")

Column L - Installation =OFFSET('Validation L'!$B$1,1,MATCH(VLOOKUP($A2, Signal_List!A:D, 4, FALSE),'Validation L'!$B$1:$C$1,0)-1,COUNTA(OFFSET('Validation L'!$B$1,1,MATCH(VLOOKUP($A2, Signal_List!A:D, 4, FALSE),'Validation L'!$B$1:$C$1,0)-1,5,1)),1)

Column M - TK =OFFSET('Validation M'!$B$1,1,MATCH(Detection!$L2,'Validation M'!$B$1:$F$1,0)-1,COUNTA(OFFSET('Validation M'!$B$1,1,MATCH(Detection!$L2,'Validation M'!$B$1:$F$1,0)-1,15,1)),1)

Column N - DO =OFFSET('Validation N'!$B$1,1,MATCH(Detection!$L2,'Validation N'!$B$1:$F$1,0)-1,COUNTA(OFFSET('Validation N'!$B$1,1,MATCH(Detection!$L2,'Validation N'!$B$1:$F$1,0)-1,15,1)),1)

[ChrispinP]

Confirmed with Erin & Joshil this is working with no issues. Closing