Technology Help + Troubleshooting with Spreedsheets
On the Chief TS Transition Doc under "Section 7: Ongoing Projects" you will find more information on different spreedsheets that were created in the 2022-23 year by Chief TS Paloma Orozco Scott (323-348-9883).
Most Common Issue
Will likely be that a column you are looking at is importing data that doesn't make sense or saying #NA. E.g. you are expecting phone numbers to be populating a column but now you are viewing the patient's last names. This is because there has been an error with the vlookup formula.
The most common formula used across the spreedsheets is vlookup, which is most likely looking up MRNs on the Master Patient list and then filling in the desired info by the column you want to see. Example: You want to see the last name of a patient that is column 3 on the Master Patient List, your vlookup formula will look like VLOOKUP(what cell you want to lookup,the sheet and range you want it to look at,the corresponding column you want to import for the value you looked up (in this case 3),and then "false") --> VLOOKUP(A3,'Clinic Teams'A1:Z900,3,false)
To address this problem identify where the formula is looking up the value you want and change the column number in the formula to the correct column you want to import from. E.g. Column 3 is importing "last names" if you inserted a column to the left of this you would need to change the formula on the other sheets from 3 to 4 bc now column 3 would be something else. Sometimes it will be looking up on a tab on the spreedsheet that is importing a data range from a completely separate spreedsheet. The below section may help you determine where info is coming from BUT you can also check the link after the =IMPORTRANGE function.
Which Sheets are Connecting to "Importing From" One Another?
You may want to refer to this if you are adding new columns to commonly used spreadsheets, to allow you to see if you have disrupted a vlookup formula in another spreadsheet.
Master Patient List: The below spreedsheets draw info FROM the MPL, if you create new columns on the MPL you may disrupt and need to edit some of the vlookup formulas on the following sheets:
1) FOR CHIEF TS ONLY Chief TS Coding Document
2) FOR ACT Act1Data on MPL
3) For GI Clinic Chief TS + Colonoscopy & Stool Testing Coordinator FIT/COLO Tracker tab titled "MasterPatientList" is using importrange fxn
4) For Gyn Clinic Chief TS + Mammography Coordinators Mammo Tracker
5) FOR LABS TS & Chief TS Labs Organizing Tool. Shingles and Pneumovax tabs columns B-H, demographic and vax status info.
TS Schedule: The below spreedsheets draw info FROM the TS Schedule, if you create new columns on the TS Schedule you may disrupt and need to edit some of the vlookup formulas on the following sheets:
1) FOR CHIEF TS ONLY Chief TS Coding Document; K6, K7, K8
Master Calendar and AMION EHHOP: The below spreedsheets draw info FROM the Master Calendar, if you create new columns on the cal you may disrupt and need to edit some of the vlookup formulas on the following sheets:
1) FOR CHIEF TS ONLY Chief TS Coding Document; CMS (K9, L9, M9), SW (K11), ACT (K12, L12, M12), ESIP (K13), Pharmacy Ancillary Clinics (K16).
Roster: The below spreedsheets draw info FROM the EHHOP Roster, if you create new columns on the roster (any sheet on the roster) you may disrupt and need to edit some of the vlookup formulas on the following sheets:
1) FOR CHIEF TS ONLY Chief TS Coding Document; TS SHEET--> TS Emails populate --> (L6, L7, L8), TS Phone #s --> (N6, N7, N8). CMs --> CM Emails populate --> (W7, X7, Y7), CM Phone #s --> (N9, O9, P9). SW contact, Act contact, esip contact, phlebotomy contact ...
2) For Labs TS/Chief TS Labs Organizing Tool. Shingles and Pneumovax tabs columns L, CCS emails from CCS sheet on roster. Looking up the CCS full name in column D.
Phlebotomy Schedule: The below spreedsheets draw info FROM the Phlebotomy Schedule, if you create new columns on the phleb schedule you may disrupt and need to edit some of the vlookup formulas on the following sheets:
1) FOR CHIEF TS ONLY Chief TS Coding Document; Phlebotomy names K15, L15
No Shows: The below spreedsheets draw info FROM the No Shows Weekly Tracker run by CMs, if you create new columns on the this sheet you may disrupt and need to edit some of the vlookup formulas on the following sheets:
Senior Clinician Schedule: The below spreedsheets draw info FROM the SC Schedule, run by student recruitment, if you create new columns on the this sheet you may disrupt and need to edit some of the vlookup formulas on the following sheets:
1) For Chief TS TS Checklist, made weekly. By the Tuesday before clinic at the latest column W should reflect which SCs and CCSs have confirmed they are in attendance.
Which Sheets Need Editing Permissions Changed During Transitions
1)For Chief TS & Chief CMs Callsheet Generator
2) For Chief TS ONLY Chief TS Coding Document