Excel Workbook Guide - Bfzcanada.ca
Canadian Alliance toEnd HomelessnessBy-Name List ExcelWorkbook GuideDecember 2020
Table of ContentsThe Guide . 1Users . 1Support . 2The Worksheets . 3Information . 3Community By-Name List . 3Data Reliability . 13Drop-down List. 14Workbook Setup: First Time Users . 15Setup Steps: Community By-Name List Worksheet . 15Setup Steps: Data Reliability Worksheet . 15Setup Steps: Customization . 16Setup Steps: Saving the Workbook . 16Storing By-Name List Data: Adding and Updating Client Records . 17Collecting Client Data . 17Sharing the Workbook . 19Adding Clients to the Community By-Name List Worksheet . 20Updating Records on the Community By-Name List Worksheet . 21Reporting Monthly By-Name List Data . 32Monthly Reporting Steps: Community By-Name List Worksheet . 32Monthly Reporting Steps: Data Reliability Worksheet . 33Monthly Reporting Steps: Saving the Complete Workbook . 33Monthly Reporting Steps: Saving the Working Workbook . 34Troubleshooting: Workbook Balancing . 35Prioritization . 38Customization . 39Additional Content . 39Drop-down List. 39Inactive Policy . 40Formulas . 42
The GuideThe Canadian Alliance to End Homelessness (CAEH) By-Name List Excel Workbook, herein referred to as‘the workbook,’ is a customizable interim approach for storing and using By-Name List (BNL) data to endhomelessness. It can be used until communities are able to fully implement a more sophisticated realtime Homelessness Management Information System (HMIS) like HIFIS 4 (the Homelessness Individualand Family Information System).The workbook is intended to be customized by communities for their unique needs. As is, the workbookmeets the basic quality requirements for both the CAEH chronic and veteran BNL Scorecards. It is alsodesigned to collect monthly Inflow and Outflow data: Actively Homeless, Newly Identified, Aged-In,Return from Inactive, Return from Housing, Move-Ins, and Moved to Inactive. More information on theBNL data points can be found in the CAEH By-Name List Data Reliability Tool & Definitions.This workbook is compatible with Excel 2007 and onward. Older versions of Excel (2003 or older) will notbe able to run this workbook properly.This guide outlines: the contents of the workbook’s four worksheets (Information, Community By-Name List, DataReliability, and Drop-down List); how to use it to store BNL data; how to use it to report BNL data; how to use it for prioritization; and how to customize the workbook.UsersThis guide refers to team members and their relationship to the workbook in two ways: Administratorsand Viewers.Administrators are responsible for managing the workbook. This includes adding and updating clientinformation directly in the workbook; pulling data for reporting; and workbook maintenance. It is crucialthat only Administrators update the actual workbook. It is strongly encouraged that there are as fewAdministrators as possible, with the ideal scenario that there is only one Administrator (with a back-up).This is because the fewer people responsible for directly managing the data in the workbook, the easierit is to manage the validity and accuracy of the data in the workbook. It is important to note that anyand all Administrators must be trained on managing this workbook as well as the BNL data points to fullyunderstand how to use the workbook.Viewers are staff who may see part, or all the workbook, but are not responsible for directly updatingthe workbook itself. Instead, they provide updates at a community table or use a standard form, whichwould then go to the Administrator who would then update the workbook directly.By-Name List Excel Workbook Guide1
SupportWhile this guide outlines the process of using the workbook step-by-step, it does not teach basic Excelskills. It is strongly encouraged that Administrators are familiar with the basics of Excel. Administratorsare encouraged to use this workbook often to become familiar and confident in how it works. The morethe workbook is used and updated, the closer the BNL is to real-time data.For support with this workbook, please contact your community support lead at CAEH (whereapplicable), or info@caeh.ca.By-Name List Excel Workbook Guide2
The WorksheetsThere are four worksheets in the workbook: Information, Community By-Name List, Data Reliability, andDrop-down List. This section of the guide covers the contents of each worksheet. To know how to usethe workbook, refer to sections: Storing By-Name List Data: Adding and Updating Client Records andReporting Monthly By-Name List Data.InformationIndicates which version of the workbook the community is using.Community By-Name ListStores client information; used for prioritization and resource matching.Data ReliabilityUses information from the Community By-Name List worksheet topopulate the BNL data points.Drop-down ListStores all drop-down list options for the Community By-Name Listworksheet.InformationThe Information worksheet provides a brief introduction to the workbook and indicates which version ofthe workbook is being used (see image below). This worksheet is password protected by the CAEH sothat we know which version of the workbook Administrators are using so that we can support theAdministrators accordingly.By-Name List Excel Workbook Guide3
Community By-Name ListThe Community By-Name List worksheet is the heart of the workbook. This worksheet stores clientinformation and can be used to prioritize clients for supports, services, and housing resources.The Main HeadingsAudience Category Field Format The top four rows of this worksheet act as the Main Headings, which lets both Administrators andViewers know the contents of each section and column.The first row (audience) divides the worksheet into two sections: Community By-Name List andAdministrator. The Community By-Name List section includes information provided by the Viewers froma formalized process: community table, intake form, etc. This is the section used to store clientinformation, filter for prioritization, and match clients to resources. The Administrator section is forAdministrators. It is where the Administrator makes notes, adjusts information for reports, and wheremost formulas are stored.The second row (category) breaks the two main sections down further into categories to help both theViewer and Administrator identify the information they are looking at.The third row (field) breaks down each category into fields wherein a record of data is collected for eachcolumn.The fourth row (format) indicates how to properly record data in each cell (see chart below for details).FormatDescriptionDatesWritten as DD-MMM-YY.DDDrop-down ListBy-Name List Excel Workbook Guide4
FormatDescriptionFill using only the text in the list provided. All drop-down lists are populatedby cells in the Drop-down List worksheet and are customizable.DNEDo Not EditThis is a self-populating cell and should not be edited. DNE cells are also ashade darker than fillable cells.Free textAdministrator can write any necessary information into this cell.It is important when filling out free text cells that there are not any spacesleft after the text. For example, when writing “John” in the First Namecolumn, do not write “John ” and leave a space after their name. Thisprohibits the workbook from identifying duplicates in select cells.Client InformationThis is the first category of the Community By-Name List section that Viewers provide information forthrough a formalized process: community table, intake form, etc. This category contains a client’s BNLstatus, attachment to resources, and basic identifying information.FieldBy-Name List statusFormatDNEDescriptionThere are six status options: Actively homeless, Housed,Inactive, N/C Actively homeless, N/C Housed, and N/CInactive. The letters N/C are a short form for noconsent. The definitions are the same for clientsregardless of consent: List managementBy-Name List Excel Workbook GuideDNEActively homeless: added to the BNL, not housed,contact within the time allotted to the inactivepolicy (default is 90 days on the workbook).Housed: someone is housed.Inactive: not housed and is not active in the systemfor one of the following reasons: left community,deceased, lost contact, staying at public institution(longer than 90 days), duplicate entry.This cell will populate based on data in the client’sspecific row.There are five status options: Documents needed,Awaiting match, Matched, Accepted referral, andHoused.5
FieldFormatDescriptionDocuments needed: added to the BNL, but still needsthe proper documents to be able to be matched toresources.The following definitions are taken directly from theReaching Home Coordinated Access Guide: Unique identifier/HIFIS clientIDFree textAwaiting match: waiting for a vacancy.Matched: matched and referred for an offer, butnot yet accepted;Accepted referral: accepted the offer and in theprogress of finding housing or moving into newhousing;Housed: the Coordinated Access (CA) process iscomplete – client is receiving housing resources andhas exited homelessness into permanent housing.This cell will populate based on data in the client’sspecific row.Unique identifiers are used to prevent duplication ofrecords. Communities determine how a uniqueidentifier is generated. Some communities have usedthe HIFIS client ID number, while others have createdtheir own unique system.Example #1: HIFIS client ID number.Example #2: Sequential 5-digit number (00001, 00002,00003, etc.).Last nameFree textClient’s last name.This cell will fill with a light red if the last name isalready on the worksheet. This does not necessarilymean that it is a duplicate entry. The cell colour changeis there to help the Administrator and signal whether ornot they need to look at a client’s date of birth toconfirm whether or not the client is already on theworksheet.First nameFree textClient’s first name.Last contact dateDateThe date that the client last made contact with thecommunity’s CA system.By-Name List Excel Workbook Guide6
Triage and AssessmentThis category includes data related to the common assessment tool(s) used by a community.FieldAssessment typeFormatDDDescriptionThe common assessment tool used by the communityto determine a client’s acuity.Assessment scoreDDThe client’s score on the common assessment tool.Assessment completedDateThe date the assessment was completed with a client.Housing HistoryThis category covers a client’s housing history. Housing history is measured in months for two specifictime periods: 12 months and 3 years. This timeline was chosen to reflect the Reaching Home definitionof chronic homelessness: 6 months or more experiencing homelessness in the past year or 18 months ormore experiencing homelessness in the past 3 years.1Data input in this category directly affects the data collected in the Data Reliability worksheet.FieldNumber of monthsexperiencing homelessnessin past year (12 monthstotal)FormatFree textDescriptionMeasured in whole months. Total cannot exceed 12months. Cell will turn light red when the number ofmonths is 6 or higher to signal to the Viewers andAdministrator that the client is experiencing chronichomelessness.Number of monthsexperiencing homelessnessin past 3 years (36 monthstotal)Free textMeasured in whole months. Total cannot exceed 36months. Cell will turn light red when the number ofmonths is 18 or higher to signal to the Viewers andAdministrator that the client is experiencing chronichomelessness.Date Housing historyupdatedDateThis cell is used to show the last time informationregarding a client’s housing history has been updated.This should be kept as up to date as possible for clientswith an Actively Homeless status.1Employment and Social Development Canada, “Reaching Home: Canada’s Homelessness Strategy Directives,”Government of Canada, Name List Excel Workbook Guide7
FieldCurrent sleepingarrangementsFormatDDDescriptionIndicates a client’s current sleeping arrangements. If aclient sleeps in multiple locations, wherever they spend4/7 nights a week would constitute the response forthis field.HousedDateThis field signals when a client is housed. To coincidewith the Reaching Home definition of chronicity, timespent in transitional housing and public institutionsshould not be counted towards chronicity.CAEH suggests that clients staying in transitionalhousing should not be considered “housed” until theyare permanently housed. However, communities canidentify transitional housing as housed depending ontheir definitions, policies, and procedures.Veteran InformationThe veteran data gathered in this category meets the requirements of the CAEH veteran BNL Scorecardfor basic quality. Clients that hold a confirmed status as a member of the Canadian Armed Forces, ally,or former RCMP meet the criteria to be included in the Veteran population table in the Data Reliabilityworksheet.Data input in this category directly affects the data collected in the Data Reliability worksheet.FieldFormatDescriptionVeteran statusDDIndicates if client holds a veteran status.Veteran status confirmedDDThis field is only to be filled for those who are veterans.VAC eligibleDDThis field is only to be filled for those who are veterans.Other Demographic InformationThe purpose of the data gathered in this category is to support the prioritization process for supports,services, and housing resources.FieldGender identityFormatFree textDescriptionClient’s gender identity.Date of birthDateClient’s date of birth. If a client does not wish to give adate of birth, but does give their age, the AdministratorBy-Name List Excel Workbook Guide8
FieldFormatDescriptioncan record their birthday as January 1 and theappropriate year for the adjacent column to calculatetheir age. Administrators can then highlight this cell adifferent colour to note that this is not their actualbirthday, rather a placeholder date to denote their age.Age calculatorDNESelf-populating cell to note a client’s age based on theirdate of birth.Head of householdDDIndicates the composition of a client’s household. This isused in addition to the Age calculator field forprioritization. For example, a youth (age 18) who alsohas a dependent (age 1) would show as a youth in theAge calculator field and family in the Head of Householdfield.Combined household(s)unique identifier(s) (ifapplicable)Free textIf a client is part of a family household and their otherhousehold member(s) are on the BNL, then the otherhousehold member(s) unique identifier(s) would berecorded here.Number of children (age 018) to be housed with clientDDThis applies only to children who are currently in thecare of the head of household.Indigenous identityDDIndicates whether someone identifies as Indigenous.Resource MatchingThis category includes information related to adding a client to the BNL and any subsequent resourcesthey are matched to and receive. The information in this category directly affects the List Managementfield. This is the last category of the Community By-Name List section that Viewers provide informationfor through a formalized process: community table, intake form, etc.FieldReferral agencyFormatDDDescriptionThis is the agency that submitted a client’s informationto be added to the BNL.Added to By-Name ListDateDate client is added to the BNL.Consent obtainedDDIndicates whether a client has given consent.Documents collectedDateDate when all documents needed to support a client inobtaining supports, services, and housing resources iscompleted.By-Name List Excel Workbook Guide9
FieldMatched to casemanagement resourcesFormatDateDescriptionMatched and referred to case management resources,but offer has not been accepted.Accepted case managementresourcesDateCase management resources have been accepted.Assigned agencyDDThis is the lead agency that will be providing the casemanagement supports that were offered.Matched to housingallowance/rent subsidyresourcesDateMatched and referred to housing allowance/rentsubsidy resources, but offer has not been accepted.Accepted housingallowance/rent subsidyresourcesDateHousing allowance/rent subsidy has been accepted.Matched to housingDateMatched and referred to housing, but offer has notbeen accepted.Accepted housingDateHousing offer has been accepted.Administrator NotesThis is the first category in the Administrator section of the worksheet. The purpose of this category is tohelp the Administrator identify when changes in the worksheet occurred. It is strongly encouraged thatthis section is completed each time a record is changed. This will help the Administrator if the data doesnot balance at the end of the month. More information on troubleshooting data balancing can be foundin the Troubleshoot: Workbook Balancing section of the guide.FieldLast Administrator updateFormatDateDescriptionThe date that the most recent change to a record wasmade.Last nameFree textAdministrator’s last name.First nameFree textAdministrator’s first name.Track changesFree textIncludes the dates, fields, and records that have beenchanged. This field keeps historical data as it pertains toa client’s record. It is also helpful for the Administratorto review if data does not balance.This should be used when cells that are populated withinformation change. This means, that if a cell goes frombeing blank to having data in it, then the AdministratorBy-Name List Excel Workbook Guide10
FieldFormatDescriptiondoes not need to record notes in Track changes.However, if a cell goes from being populated with datato being blank, then the Administrator would recordthis in Track changes because the information in the cellis being erased and should be recorded elsewhere.Track changes should be recorded in the following way:Date change occurred – field (previous record to currentrecord).Example: October 10/2020 – current sleepingarrangements (Shelter to Permanent and/or long-termhousing).By-Name List Monthly DataThis is the final category in the Administrator section of the worksheet that only Administrators use. Thepurpose of this category is to support the Administrator in reporting monthly data. The bulk of thiscategory is formulas used to populate the Data Reliability worksheet. The table below provides briefdescriptions of the fields. More information on these fields and how they work and are to be used canbe found in Updating Records on the Community By-Name List Worksheet section of the guide.FieldCalendar month for datacollectionFormatDateDescriptionThis is the most unique cell in the worksheet. The datein row 3 (a cell that is reserved for format informationfor every other field), is meant to be recorded as thereporting month.Example: If an Administrator is working on completingthe report for June 2020, they would be entering June2020 data into the worksheet. This includes new clientsand updates to clients already recorded in theworksheet. This cell should read: June 2020.More information on how to use this cell is located inthe Setup Steps: Community By-Name List Worksheetand the Monthly Reporting Steps: Community By-NameList Worksheet sections of the guide.Data pointBy-Name List Excel Workbook GuideDDThis field indicates which BNL data point is applied tothe record and speaks to the Data Reliability worksheet.11
FieldData point dateFormatDateDescriptionThis field reflects the date that the event in the Datapoint field occurred.Inactive policy timeline (90days)DNEThis field is broken into two columns: date andinformation. They indicate when a client would beconsidered inactive according to a community’s inactivepolicy. The default timeframe that this workbook uses is90 days without contact for a client to be consideredInactive. This 90-day timeline can be changed by theAdministrator. The 90-day timeline is based on a client’sLast contact date.There are three status options: Actively homeless,Actively homeless - nearing 90 days without contact,and Inactive. Actively homeless: client has contacted the systemwithin the last 90 days of the reporting month. Actively homeless - nearing 90 days withoutcontact: If the client does not contact the system bythe end of the reporting month, they will beInactive. Inactive: The client has gone longer than 90 dayswithout contacting the system.This field breaks down the month and year from theAdded to By-Name List record to be used for the DataReliability worksheet.Added to By-Name List dateDNEHoused dateDNEThis field breaks down the month and year from theHoused record to be used for the Data Reliabilityworksheet.Data point dateDNEThis field breaks down the month and year from theData point date record to be used for the DataReliability worksheet.Chronic dataDNEThis extracts data from the Housing history category toindicate whether a client is experiencing chronichomelessness. This information feeds into the DataReliability worksheet.Veteran dataDNEThis extracts data from the Veteran informationcategory to indicate whether or not their data is to beentered into the veteran section of the Data Reliabilityworksheet.All population dataDNEThis data speaks to the Data Reliability worksheet.By-Name List Excel Workbook Guide12
Data ReliabilityThe Data Reliability worksheet pulls data from the Community By-Name List worksheet into the BNLdata points. It sorts the data into three tables of three different data sets: chronic, veteran, and allpopulations.ChronicOnly clients with a Housing History of either experiencing homelessnessfor 6 months or more in the past year and/or 18 months out of the past3 years are counted in this table.VeteranOnly clients who are confirmed as veterans with the following statusesare counted in the table: Canadian Armed Forces, ally, civilian, andformer RCMP.All populationsAll clients are counted in this table.Each table has three rows: data points, Past month for balance check (static values), and Current month(dynamic formulas). The Past month for balance check row includes the values of the previous month’sdata points. When using this workbook for the first time, the values will be zero.The Current month row includes formulas that will pull data from the Community By-Name Listworksheet according to each respective data point. To understand the criteria of each of the BNL datapoints and how data balancing works, please refer to the CAEH By-Name List Data Reliability Tool &Definitions.By-Name List Excel Workbook Guide13
Drop-down ListThe Drop-down List worksheet contains all the drop-down list options that are used in the CommunityBy-Name List worksheet. Each list starts with a shaded and bolded title row. The order of each listfollows the order as it the field appears in the Community By-Name List worksheet. Administrators cancustomize the drop-down list options. More information on customization can be found in theCustomization section of the guide.By-Name List Excel Workbook Guide14
Workbook Setup: First Time UsersIf it is the first time a community is using the workbook, their Administrator must edit the workbookfrom a template to a tool that is tailored to their community. This includes changing the workbook’sdefault dates to reflect the community’s current reporting month on two worksheets: Community ByName List and Data Reliability. Additionally, Administrators can customize the drop-down lists based ontheir community’s unique needs and agencies in the Drop-down List worksheet.Setup Steps: Community By-Name List WorksheetOnly one field needs to be edited on the Community By-Name List worksheet: Calendar month for datacollection. The yellow shaded cell immediately below the field should be changed to the currentreporting month. For example, if the Administrator were to be inputting data for July 2020, andreporting on July 2020 data, then the cell should read: July 2020.Setup Steps: Data Reliability WorksheetEach population table must reflect the previous reporting month in the values of the Past month forbalance check row, and the current reporting month in the formulas of the Current month row. Whenusing this workbook for the first time, the values in the Past month for balance check row will be zero.For the Current month row to calculate properly, the formulas need to reflect the proper reportingmonth and year. The process for adjusting formulas is the same for each population table and eachpopulation table should be adjusted at the same time. Below are the steps for adjusting the tables tocollect the appropriate data.1) Use the Replace function (Ctrl H) to find and replace the month that is currently in theformulas with the reporting month. The image below illustrates how the Replace functionwindow would look like if an Administrator were to use the original workbook for the first timeto report July 2020 data.By-Name List Excel Workbook Guide15
2) Use the Replace function (Ctrl H) to find and replace the year that is currently in the formulaswith the reporting year.3) Change the date in the Month columns to reflect the previous month and current reportingmonth. In this case, the Past month should be June 2020 and the Current month should be July2020.Setup Steps: CustomizationAdministrators can customize the entire workbook to meet their needs at any time. However,Administrators must be careful not to break formulas. More information on how to customize theworkbook can be found in the Customization section of the guide.The first drop-down list that Administrators may want to change is the Agency list. The options arecurrently used as placeholders (Agency 1, Agency 2, and Agency 3) for Administrators to edit to namethe agencies that involved in their BNL process.For example, if the Riverdale Shelter is an agency adding to a community’s BNL, the Administratorshould change Agency 1 in the drop-down list to say Riverdale Shelter.Setup Steps: Saving the WorkbookEach month, the workbook should be saved as a new file. This is to help reflect on past data, as well asto support the Administrator should they need to review past data in case there is an issue with databalancing in future months.When an Administrator starts inputting data into the workbook for the first time, the file should besaved as a working workbook. Building off the example above, if an Administrator started using theworkbook for July 2020 data, the file should be saved as: Community By-Name List (July 2020 – working).More information on saving working and complete workbooks can be found later in the guide.By-Name List Excel Workbook Guide16
Storing By-Name List Data: Adding and UpdatingC
By-Name List Excel Workbook Guide 2 Support While this guide outlines the process of using the workbook step-by-step, it does not teach basic Excel skills. It is strongly encouraged that Administrators are familiar with the basics of Excel. Administrators are encouraged to use this workbook
Excel 5.0 Excel 5.0 1993 Excel 5.0 1993 Excel 7.0 Excel 95 1995 Excel 8.0 Excel 97 1997 Excel 98 1998 Excel 9.0 Excel 2000 1999 Excel 2001 2000 Excel 10.0 Excel XP 2001 Excel v.X 2001 Excel 11.0 Excel 2003 2003 Excel 2004 2004 2.1.2 Worksheet Document Definition: Worksheet Document A worksheet document consists of a single sheet only.
Click the Excel 2019 app to run the Excel app and display the Excel start screen Click the Blank workbook thumbnail on the Excel start screen to create a blank Excel workbook in the Excel window-7-Starting and Using Excel (3 o
Power Map Power Map provides a new perspective for your data by plotting geocoded data onto a three-dimensional view of the earth and optionally showing changes to that data over time. To use Power Map, you import raw data into a Microsoft Excel 2013 workbook, add the data to an Excel data model, and enhance the data in the data model if necessary.File Size: 1MBPage Count: 17Explore furtherGetting an excel list of all Azure Virtual machinesdbaharrison.blogspot.comDownload Azure Devops Board To Excelwww.how-use-excel.comGetting an excel list of all Azure Virtual machines .www.firstcloud.ioGetting an excel list of all Azure Virtual machines .laptrinhx.comRunning Excel On Azurewww.how-use-excel.comRecommended to you based on what's popular Feedback
Excel will not be able to open an Excel 2007 worksheet unless you save it as an Excel 97-2003 Format. To use the Save As feature: Click the Microsoft Office Button Click Save As Type in the name for the Workbook In the Save as Type box, choose Excel 97-2003 Workbook Open a Workbook To open an existing workbook:
Microsoft Excel Basic 1. Setting up a workbook Creating Workbooks o Excel Terminology o Excel Environment o Customizing Quick Access Toolbar o Creating a New Workbook o Entering Data into Workbook o oSaving a workbook o Open a workbook o Zoom o Insert a new worksheet o Renaming a new worksheet o 3.Changing the tab colour of worksheet
While Excel 2010 documents share a file extension with Excel 2007 (*.pptx), the Excel 2010 file is a unique file type. Excel 2007 documents will open in “Compatibility mode” and will not have certain Excel 2010 tools available unless re-saved as an Excel 2010 document. Saving a Excel
In this task, you will create a new Excel workbook that will be used to develop the Power Pivot data model and create a report. 1. To open Excel, on the taskbar, click the Excel program shortcut. 2. In Excel, to create a blank workbook, click the Blank Workbook template. Figure 2
I am My Brother’s Keeper (2004) As our New Year’s celebration draws near, I once again find myself pondering the enigmatic story that our tradition places before us at this time—the story of the Binding of Isaac. Once again, I walk for those three long days with father Abraham and ponder the meaning of his journey with his son to the mountain. And once again, I find fresh meaning in the .
