Home

Data2Maps (D2M) User Manual – July 2009

1. Q Q Q Q a Series ISeries1 oo Name n 0 Series2 avg st dev F3 i E3 RTS 5 fcr ES exceed l ae fell gcrgso craze Z data pts 230 mgA 0 Category X axis labels CHARTSI CQ 30 CQ 422 t Site Year Date Second category x axis labels ADMIN_MAP A 11 4 12 8 11 In the Category X axis labels box of the Series tab change both occurrences of CQ to the column letters of your new date info in this case you would enter CV Cx CY CZ DA DB DC DD DE Source Data ede Teal Site 17 250 200 2 150 2 A 100 3 50 ae wo wo wo wo o o O O O o Ss 8 a a Series Seriesi o n 0 Series2 avg st dev 1ARTS CR 422 ABs eit Values TSISCR 30 CR 422 data pts gt 230 mg a Category X axis labels CHARTS CQ 30 C93422 S t Site Year Bacteria Event Second category X axis labels ADMIN_MAP A 11 A 12 S 8 12 Repeat Steps 8 1 8 13 for each of your additional charts 8 13 Click OK 8 14 Save your file You have now set up the data filters to look for your Site IDs and the charts are all pointing at the correct data filter results 27 STEP BY STEP How to Set Up D2M for Your Project Set Up Embedded Charts in MAP OPTIONS 1 and 2 In this section you will set up the charts in the Map Option worksheets This entails 1 Adding or deletin
2. Dissolved Phosphorus Summary Analysis This is where parameter specific comments go Site 2 3 1 Map with sampling locations color coded by how often the standard was exceeded during the time period you specified Red gt 1 exceedance at this location Yellow 1 exceedance at this location Green No exceedances at this location 2 Choice of which map you want to use to view your sampling locations in the template provided Show Map is the example seen above Show Image is an aerial photo and show Municip uses the map and delineates municipal boundaries 3 Comments caveats specific to a parameter 4 Charts for each of the sampling locations are displayed on all of the map chart worksheets except for Map Option 3 The charts include a bar to indicate the user defined standard threshold Overview of Data2Maps 4 STEP BY STEP How to Use Data2Maps This chapter will guide you step by step through how to open your D2M file enter data query by parameter and year change your map displays and print export your output These steps only require a basic knowledge of Excel and assume that your D2M file has already been customized for your project Setting Excel s Security Level and Opening D2M Before opening a D2M file you will need to set your Excel security level setting to medium D2M was developed using macros and VBA Excel s programming language a high security setting will not allow th
3. 13 2 Go into the formula bar and delete all datesXX where XX is greater than your number of sampling locations For example if you have 4 sites the formula would look like MIN dates01 dates02 dates03 dates04 31 13 3 Select cell A12 13 4 Go into the formula bar and delete all datesXX where XX is greater than your number of sampling locations For example if you have 4 sites the formula would look like MAX dates01 dates02 dates03 dates04 31 13 5 Save your file 13 6 Skip to the section Align Maps with Sampling Locations on pg 42 STEP BY STEP How to Set Up D2M for Your Project 40 14 If you have more than 16 samples 14 1 Select cell A11 14 2 Go into the formula bar and for each additional site you have above 16 add datesX X separated by commas For example if you have 18 sites the formula would look like MIN dates01 dates02 dates03 dates04 dates05 dates06 dates07 dates08 dates09 dates10 dates11 dates12 dates13 dates14 dates15 dates16 dates17 dates18 31 14 3 Select cell A12 14 4 Go into the formula bar and for each additional site you have above 16 add datesX X separated by commas For example if you have 19 sites the formula would look like MAX dates01 dates02 dates03 dates04 dates05 dates06 dates07 dates08 dates09 dates10 dates11 dates12 dates13 dates14 dates15 dates16 dates17 dates18 31 14 5 Save your file Your charts will now correctly display the minimum maximum
4. E g Site ID 466456 951 25 499454 00422 490625 10276 493721 69452 49391 4 05612 494703 15399 492991 19339 489238 33397 491 284 54867 489646 50167 480465 7 7525 486634 70135 491395 03641 489250 62189 40901 64 07 760 4arogr 107 S35 459081 7 18004 4590649 2606 4590022 36706 45060503 66406 4560065 24067 4569609 0056 4 4591 681 37514 4595402 32655 4599304 26109 4597499 316061 4601 063 62652 4602711 16222 4600937 66276 4603640 36456 45941 33 59349 4605355 56002 5 6 Click on the button to return to the Define Name dialog box 5 7 Click on the Add button Do not forget this step otherwise no change will be made 5 8 Click on the Close button to close the dialog box 5 9 Save your file STEP BY STEP How to Set Up D2M for Your Project 34 If you have more than 16 sites you will now add Excel names for each new site If you have 16 sites or less SKIP to the next section Modify VBA Code on pg 37 6 For each additional site on the CHARTS worksheet identify the date and parameter columns For example if you added a 17 site it would be located under crit17 on the CHARTS worksheet and the date column would be CV and the parameter column would be CW crit Site Year Bacteria 17T z gt HA lt gt HA No sample data 1 A m A m iam iam iva wo co Co co co co co Co Co Co Co co Co oe tbe filo i i om be be oo oc m 0 J exceed data pts 230 mgl Oo Site Year
5. 22 Click Paste on the Edit menu The following picture shows Map Option 1 pasted into Microsoft Word Mac users instead of steps 19 22 you should select Copy in Excel then in your other Microsoft application select Paste Special and Paste as Picture E Document Microsoft Word DAR i File Edit view Insert Format Tools Table FlashPaper Window Help AdobePDF Acrobat Comments Type a question for help X NDZ Bk 7 S a EE T5 Read i E ELA All Sampling Datafor DO 2006 Standard 05 mgA last upciated Aune 27 2007 3 Ammonia Analysis One key hrg lo nek is hal be amonia s lawiad b ochialy arange bared on Temperakre and pH conditions al he ine of sapling Conce Fallon Trom 0 5 2 mgl Hi be hreshold hal woud vitak he slandard The Iverhold in bese graphs har been sellal 05 mgl te acomemalve revkw lt Page 1 Sec 1 1 1 At 0 8 In 3 Col2 To make adjustments to an image pasted in a Microsoft application use the Picture toolbar Go to View gt Toolbars and select Picture STEP BY STEP How to Use Data2Maps Printing Printing Maps the map worksheets already have the appropriate print area set up 23 To preview a page before printing go to File gt Print Preview 24 To print a page go to File gt Print Printing Charts Before printing charts you may need to adjust page breaks so that they do not cut off the charts or list of data points 25 Go to File gt Print Preview to se
6. Ewent You will need to define 4 names for each site you add They are listed below along with the formula you need to paste into the Refers to field in the Define Name dialog box XX critXX in CHARTS worksheet dataXX paste in CHARTS parameter column 29 parameter column 433 for crit17 the name data17 would refer to CHARTS CW 29 CW 433 datesXX paste in CHARTS date column 30 date column 430 for crit 17 the name dates17 would refer to CHARTS CV 29 CV 433 critXX point to CHARTS Worksheet 3 columns and 2 rows under critX X see following picture rangeXX point to CHARTS Worksheet Data headings for critX X see following picture 35 STEP BY STEP How to Set Up D2M for Your Project criti r cit 17 Ho sample data 1200 1000 500 600 400 200 0 AA 2006 7 6 2006 FA 52006 72272006 7292006 a5 2006 6 22006 6 92006 6 26 2006 o 2 2006 m O avg at dev To exceed data pts 1000 mg diss solids 0 rangel 7 Add the 4 new names for each new site using the following steps 7 1 Open the Define Name window Insert gt Name gt Define 7 2 Type the new name in the Names in Workbook box 7 3 Click on the button under the Refers to 7 4 Select the appropriate worksheet and cells 7 5 Click on the button to go back to the Define Name dialog box 7 6 Click on the Add button Don t forget to do this step Otherwi
7. HA lt gt HA Ho sample data y 250 L 200 t150 s 100 L 50 i 0 1 ao oo oo mo oo ao 8 8 8 8 8 8 3 E 8 a gg z m4 D F F oo oo i i n O i avgi i at dew i exceed i i data pts 230 mol D L i i L 8 3 Paste in your selection criti 6 Site Year Bacteria Site Year Bacteria 16 lt HA lt gt HA 16 lt HA lt gt HA Ho sample cata Ho sample data 1 250 4 i 200 200 i i 150 150 t100 100 i l 50 50 0 0 4 do do mo do pa pa a pa do do do do 8 8 8 8 B 8 Ss 8 8 amp 8 F 8 amp amp 6 amp 8 amp amp a T 5 im T 5 1 F F oo oo F b oo oo 4 n 0 n O i avg avg i at dev at dev i exceed gt exceed i vt data pts 230 maa data pts 230 mgA a 0 i 4 Site Year Date Bacteria Event Site Year Date Bacteria Ewent i i STEP BY STEP How to Set Up D2M for Your Project 8 4 Select the cell with the pink crit16 label in this case CT5 Change this to critXX where XX is the number following the crit label from the previous location in this case you would enter crit17 CT CW Fil 2 jcrti6 ert T 1 Site ID Year Ammonia nitrogen Site ID Year Ammonia nitrogen r 16 lt gt HA 16 lt gt HA 8 5 Select the cell that is 2 cells below the pink critX X label in this case CT7 This cell defines the site name for this site 8 6 Look in th
8. Iadi a2 I In Column D Crit Num enter the next consecutive number While this column is not used in the query or charts it does help you associate each site with the correct chart formula code etc Paste the following formula in Column H EXCEED IF COUNT dataXX 0 9999 DCOUNT dataXX param CritLimit Change the 2 XX s in that formula to the associated Crit Num Column D To see an example select Column H for a previous site ae i f amp F COUNT data16 0 9999 DCOUNT data16 param CritLimit D F G LONGITUDE LATITUDE Site ID x Y EXCEED 1 488456 93125 4590817 18004 0 2 2 489454 00422 4590849 28806 0 3 490825 10278 4590822 36706 0 4 49372169482 4588583 86408 0 5 5 493914 08612 4588865 24067 0 6 6 48470315399 4589688 00564 0 l 7 4929 4591681 57514 0 8 8 459540232653 9999 9 9 491284 84887 4599384 28189 0 10 10 489646 50167 0 11 11 488465 77525 0 12 12 486634 70135 0 13 13 491395 03641 0 14 14 489250 62189 9999 15 15 488184 87760 4594133 89849 9999 16 16 48784710733 4605335 58882 9999 l 6 Save your file STEP BY STEP How to Set Up D2M for Your Project 20 You have finished entering the names and latitude longitude information of your monitoring sites 21 STEP BY STEP How to Set Up D2M for Your Project Set Up Data Filters and Charts on CHARTS Worksheet The data queries in D2M use customized Excel data filters that are set up in the Ch
9. avg 804 00 st dev exceed 0 n 2 avg 599 00 st dev 2 83 exceed 0 data pts 1000 mgA diss solids data pts 1000 mg diss solids 0 0 Site Year Date 14 2006 8 2006 804 15 2006 8 2006 15 2006 8 22 2006 Conductivity Event Site Year Date amp amp amp amp Re F RG amp Fa K F CHARTS worksheet This worksheet shows details of each sampling location The charts on this worksheet are larger than those on the map displays and have the clearest labeling Underneath each chart are summary statistics for the location and a list of the individual data points includes date value storm events Conductivity Event The remaining worksheets are general instructions and admin worksheets used for coding Overview of Data2Maps 2 Entering Data During the set up process the column headings in the Data Entry worksheet will be labeled with your sampling parameters You enter your data directly into this worksheet whenever you get a new set of data Every time you run a data query all the maps and charts are refreshed with the data entered in this spreadsheet E Microsoft Excel D2M_template_v2 xls BAR Fed File Edit View Insert Format Tools Data FlashPaper Window Help Adobe PDF Type a question for help X MAMA MEIE IA E me Rae A a ESEE Z alo aO with Changes End Review DATA ENTRY SHEET Once the sheet has been customized for your project you can enter data starting in row 8 D
10. be distorted 35 First we will get an idea of how much the samp data chart needs to be moved 35 1 Click on the SHOW MAP button This will bring the samp data chart to the top layer with map1 visible underneath SHOW MAP SHOW IMAGE SHOW MUNICIP 35 2 Compare your sampling locations on the samp data chart with the locations on your map to determine what direction the samp data chart needs to be shifted and how much it will need to be re sized HINT Focus on aligning sampling points on opposite ends of your extent When those look correctly aligned they you can confirm that the points in between are aligned too In the example below we are focusing on site 16 and site 4 We want to move the samp data layer until those two sites in the samp layer circled in red are on top of the two equivalent sites in map circled in green I can see from Site 16 that samp data needs to be moved up and to the left Then looking at Site 4 tells me that I will probably have to make samp data longer and a little bit narrower All samples in compliance eye h 1 sample not in compliant gt 1 sample not in complian 12 Mo Data i 9 ry 13 i 14 Gp 15 STEP BY STEP How to Set Up D2M for Your Project 48 36 Move and re size the samp_ data chart as needed 36 1 Select the sampling locations chart area by entering samp _ data in the Name box or by holding down Ctrl while clicking on one of the sam
11. in MAP OPTIONS 1 and 2 Re Define Excel Names Modify VBA Code Define Minimum Maximum Dates for Charts Align Maps with Sampling Locations As you can see the set up procedures require working with Excel data filters names charts formulas and VBA code While we try to give as detailed instruction as possible this section will be much less frustrating if done by an experienced Excel user Make sure you have all the items in the checklist below and then start on the next page Checklist before You Start The D2M Excel file D2M_ template vXX xls A list of your sampling parameters and if available units and relevant standard thresholds A list of your site names and x y coordinates Unprojected coordinates lat long will be easiest to match up to maps Map s that include sampling locations for reference Give Appendix B Guidelines for Creating Maps for Data2Maps to your GIS person Decide which Map Option s you will be using Setting up each Map Options is time consuming so you should only customize the ones you will be using NOTE If you have 10 or more sampling locations we recommend you only use Map Option 3 Map Options 1 and 2 include charts for each sampling location and are hard to read with too many sites Have what you need Then let s begin 13 STEP BY STEP How to Set Up D2M for Your Project Enter Parameter Information In this first section you will replace
12. testing may be needed since in some cases the d D AMMOCE nitrogen 05 mA Anaona Anaby sx a Caney boa Ot me One bey thing to note is that the amonia standard is actusty orange based j Siseme 15 the WH at smaping Concetrations from 0 5 2 mg4 could be the threshold tha a E e wr wr 5 N these graphs has been set at 05 mgA for a conservative revarw i a nae Bactera 230 ng Chicride Summary Aratysis i ie kze 6 This it where Anatytis Sutenary goes Conductivty 1000 mogi diss sobds Conductivity Summary Anshis ua 7 This it where analysis summary goes 9 o ira 4 ngt 00 Suemery Analysis w Reminder in thes cate the standard is the Pe oxygen concertrabon shoud y 8 the threshold in this case y E co 235 efuriO0 mi E cob Sutenary Analysis 2 t 20 t is tarty obvious that E col is a concern Dissolved Phosphorous data ca ue A bs 9 phospherusus of greater than 51 is a possitle racao of anthropogers H t we 10 Pow OS cts 3 x TT m 22 Macrobii Index of Bote rtegty H emear R her Nitrate Ntrte 10 mgt Ntrote Anatysis i e ea 12 Anatyns resuts ndicate N rate s wel below the state standard 13 pet 7 Tots phosphorus No stste sadad mgt Total Prosphreus Aratysis Stee ran 1 we etn e cn own eee eer This it where anelysis susenary goes Total suspended sobgs No state standard mgA Total Suspended Sokis Analys 16 Thes amp where anatysis sumenary goes 17 Select Param Info Worksheet 13 H gt Instructio
13. the D2M default water chemistry and other parameters with your own project s parameters Up to 30 parameters can be used in Data2Maps NOTE If you are adding or changing parameters to a file you have already set up skip to page 18 and see the supplemental guidance Otherwise you run the risk of loosing data 1 Before opening the D2M file make sure your Excel security level is set no higher than medium to ensure that the macros can run see Setting Excel s Security Level and Opening D2M on page 5 2 Open the File called D2M template vXX xls Save as D2M_yourproject xls 3 Select the worksheet Param Info by clicking on the PARAM INFO tab on the bottom of the page E Microsoft Excel saltcreek 311_template 06132007 xis Edt yew peet Fom Jods psa Window Help DA N JA E l Gy th a tom m oa gt 4B gt Ty Reni with Charoes End Review A 1 _ To enter ating break click ALT ENTER Mops wil show STANDARD stascardtivestow entered units entered wth the following exce 1 1 PARAM is Cissctved P maps wil show THRESHOLD standarditivestold entered 7 ar C comgiance 2 2 H UNITS is blank e g pH maps wil show STANDARD stasdardtireshoki entered STANDARD uats 3 PARAM THRESHOLD label for y axes COMMENTS Oissotved P 051 Cessotved Phosphorus Summary Analysis Cissoived P is not a State standard Rather the threshold of 1 dissolve 4l weste problems Further
14. 07 4 05 PM 16 All samples in compliance 14 1 sample not in compliance Sit 12 gt 1 sample not in compliance 12 No Data ei 93 Site 11 Site 15 Site g Site 1 Dissolved Phosphorus Summary Analysis This is where parameter specific comments go Site 2 3 1 Map with sampling locations color coded by how often the standard was exceeded during the time period you specified Red gt exceedance at this location Yellow 1 exceedance at this location Green No exceedances at this location If a location had no monitoring data it will be displayed as a hollow square 2 Choice of which map you want to display your sampling locations on assuming your D2M file was set up with multiple maps e g graphic map aerial photo 3 Comments or caveats specific to a parameter are displayed under the maps these comments are entered in the Param Info worksheet 4 All the map chart worksheets except for Map Option 3 display charts for each of the sampling locations These charts include a bar to indicate the user defined standard threshold All the charts in a worksheet will have the same start and end dates start date earliest sampling date over all sample locations end date latest sampling date over all sample locations 9 STEP BY STEP How to Use Data2Maps Exporting You can paste the map charts from D2M into another application e g PowerPoint MS Word as a picture 18 On the map or chart work
15. 4 4 If not all of the text is showing you can resize the textbox by grabbing one of the handles of the selected textbox and dragging it until the entire text is visible 44 5 Select the label again by clicking on the hash mark border and drag it next to the associated sampling location 44 6 If your project has exactly 16 locations SKIP to Step 47 1 STEP BY STEP How to Set Up D2M for Your Project 52 45 If you have less than 16 locations 45 1 Select each extra label and delete 45 2 Select any label right click and choose Grouping gt Regroup 46 If you have more than 16 locations then for each additional label 46 1 Select one of the labels 46 2 Copy and then paste onto the map 46 3 Follow the directions in Step 44 to rename the label 46 4 Position the label next to the associated sampling location 46 5 Repeat Steps 46 1 46 4 until all your labels on entered and correctly positioned 47 Label Features optional You may want to label or highlight relevant features on your map e g outfalls facilities 47 1 To add other text labels to your map add and modify new labels following Step 46 48 With all your labeling finished you will group them together so that they all appear in the top layer on top of your maps and samp data layer 48 1 Select all the labels by holding down shift and clicking on the individual labels 48 2 Right click on one of them and choose Grouping gt Group 49 Save you
16. 6 29 2005 A Across from Splashdown Dunes base Insert Comment 60 6 29 2005 B Munson Ditch at US 12 540 base 61 6 29 2005 1B Waverly Road base T Format Cells 62 6 29 2005 2 West Tributary 3985 base l 83 6 29 2005 3 Dunes Creek pre culvert 7407 base fe tule Sp kel 64 6 29 2005 4 Dunes Creek Outlet base Add Watch 65 6 29 2005 5 Great Marsh Tributary base 56 62972005 6 East Tributary Hawleywood Road base Greate List Of 6 29 2005 7 East Tributary downstream of US20 487 base D Hyperlink 50 6 29 2005 8 East Tributary upstream of US20 base i 69 6 30 2005 A Across from Splashdown Dunes base a4 Look Up 90 6 30 2005 B Munson Ditch at US 12 840 base 13 Click on the Transpose checkbox and then click OK Paste Special Paste all validation Formulas All except borders values Column widths Formats Formulas and number formats Comments values and number formats Operation None Multiply Add Divide Subtract C Skip blanks Cancel 14 Make the selection your parameters bold for ease of reading Your parameters are now listed as bold headings in the Data Entry worksheet If you want you can also update the units in row 6 of the Data Entry worksheet by repeating Steps 9 13 using column C from the Param Info worksheet That is an optional step since all units in the Map Option and Charts worksheets refer to the Param Info worksheet 15
17. 6352 12 SUIC 456634 70135 460271116222 EXCEED amp A 3995 amp you would enter use BBEAR as the sitename under crit01 on the CHARTS worksheet BEAR as the sitename under crit02 BPINE as the sitename under crit03 etc 6 Save your file If you have exactly 16 sites in your project skip to the next section on pg 28 23 STEP BY STEP How to Set Up D2M for Your Project ie 8 If you have less than 16 sites 7 1 Select the columns containing the extra sites and hit Delete criti Site Year E coli 11 lt gt NA lt gt NA No sample data 250 200 150 100 50 0 wo wo wo wo wo wo wo wo wo wo o o o o Oo o o o o o o Oo o o 9 ago gg gggqgaqa T Format Cells 8qggess Es Sf ps oe oo Column Width n 0 Hide avg st dev Unhide exceed data pts 235 cfu 00 ml 0 t Site Year Date E coli Event ms Param Info Data MAP OPTION 1 MAP OPTION 2 MAP OPTION 3 5 5 7 2 Save your file 7 3 Skip to the next section on pg 28 If you have more than 16 sites you will need to copy and paste in more columns 8 1 Select and copy Columns CO CS STEP BY STEP How to Set Up D2M for Your Project 24 8 2 Go to the first column after the last Event column in this case CT click on the top cell oT Site Year Date Bacteria crit 6 iSite Year Bacteria 16 lt
18. 7 56276 o 14 489250 62189 4603840 38456 9999 15 486184 87760 459413369849 9999 16 487647 10733 4605535 56662 9999 4 Ifyou have less than 16 sites 4 1 4 2 4 3 Select the cells in Columns D H that contain extra sites do not select entire column Right click and choose Clear Contents Or if you choose Delete make sure you choose Shift Cells Up when prompted Skip to the section Set Up Data Filters and Charts on CHARTS Worksheet on pg 22 J K mapdata LONGITUDE LATITUDE No exceedances Crit Num Site ID x Y EXCEED mapcriti 1 1 488456 93125 4590817 18004 0 EXCEED 2 2 489454 00422 4590849 28806 0 0 3 3 49082510278 4590822 36706 0 4 4 49372169482 4588583 86408 0 Site ID x 5 5 493914 08612 4588865 24067 0 1 488456 931 6 6 49470315399 4589688 00564 0 2 489454 00 T 7 49299119339 4591681 57514 0 3 490825102 a 8 489238 33397 4595402 32653 9999 4 493721 69 491284 84867 4599384 28189 5 493914 08 10 10 489646 50167 4597499 31861 6 49470315 11 11 488465 77525 4601083 62632 saranta 12 12 486634 70135 4602711 16222 13 13 491395 03641 4600937 86278 14 14 48925062189 4603840 38456 15 15 488184 87760 4594133 89849 48784710733 _4605335 58882 Paste Special Insert Delete Clear Contents N Ld Insert Comment A Format Cells Pick From Drop down List Create List D Hyperlink 5 Ifyou have more than 16 sites then for each new site
19. Data2Maps D2M a custom Excel tool for displaying your monitoring data User Guide USEPA Region 5 rev July 27 2009 Table of Contents rodie iON Dae enee nen en Sere een one t a E none mentee cere CeeR rc eNe mE 1 OVEN 1G WO a2 NAS einna e E euticeoine dea E A O E T l P2 M Works DECIS E E O l PET Dati sonaron e S teeta E E 3 Quc me Datanet e EA 3 NTC Wt INC SUG eaer E e A AE 4 STEP BY STEP How t Use Data2 Maps onorioririinia in A nA OTA RAAN TE 5 Setting Excel s Security Level and Opening D2M cc ccssssssseeseeeeeeeeeeeeeeees 5 Entrimo Dataene i aE astene E Wishart E E de E 6 Customizing Parameter Monna Noea E ee 7 Quern ne Data nones noten chadidnah ie tulet da dualiiede a gh ital yl ta lsatiiie dele tends 8 NTS Wy IVa ANUS i siesestic ctersn tress E E 9 ESPOLI ose meme Rennes Pree mee ee Ae Oe E eres er ee On ss eee mee ere 10 PEONO arenino aeneae aati asus E E AEAEE S Ceo EEE antl TEE tes EE 12 STEP BY STEP How to Set Up D2M for Your Project ccccccccccecccceeeeeeeeeeeeeeaeees 13 CSCI IST betore YOU Star berer n nE EO OEE A OEE 13 Enter Parameter IDT Ma Oies A E A 14 Enter Site Names with Latitude Longitudes nnnn0noee0sooooeeennnnnnnssssssssssseeeeereeeereo 19 Set Up Data Filters and Charts on CHARTS Worksheet 0ceeeeseeeeeeeees 27 Set Up Embedded Charts in MAP OPTIONS 1 and 2 0 0 cccccecsseseeeeeeeeees 28 RG Delme Excel NAMES serrr Te E TAT aA T 31 Modi YNV BAL odece a E E N 37 Def
20. General Tab on the bottom under Options make sure that the width and height do not exceed the allowable size for the Map Option your requestor has chosen see below If not close the dialog make your view smaller and start at Step b again Map Option 1 max width 340px max height 385px Map Option 2 or 3 max width 500px max height 575px 6 Save the files as GIF files 7 Ifrequestor is asking for more than one map repeat Step 5 with the desired layer Note Do not change the extent of your view as you want all the maps to cover the same extent so they can be layered on top of one another 8 Ifrequestor does not already have a map with sampling sites labeled please make one for them to use in the set up process Appendix B Creating Maps for Data2Maps
21. P How to Set Up D2M for Your Project 22 2 Select cell C7 This cell defines the site name for your first site 3 Look in the formula bar to see the formula for this cell was 1 so this formula shows ail lt z gt HA E 1 sample not in compliance 2 0 SSS The sampling site name in the ADMIN MAP worksheet associated with crit01 crit num 01 i l 99 4 If your first site the Site ID next to crit num 01 is not also named 1 then retype the formula with the site ID so that it looks like site ID where site ID is the site you have listed on the ADMIN MAP worksheet next to crit01 cell D3 It must be typed identically for the Refresh Charts Maps button to work correctly so you may want to copy the names from the ADMIN MAP worksheet and paste it into the CHARTS worksheet 5 Repeat step 4 for each of the sites you listed in the ADMIN MAP worksheet For example if your site IDs on the ADMIN MAP worksheet looked like this Se eee eee eee mapdata LONGITUDE LATITUDE crit_num Site ID x Y 01 BBEAR 456456 93125 4590617 1 5004 02 BEAR 439454 00422 4590849 256806 03 BFIME 490825 10270 4590822 36706 04 HIGH 493721 69482 4556583 56408 05 M55 49391 4 05612 4558565 24067 Ub MANO 494703 15399 4559685 00564 U MARSH 492991 19339 4591681 57514 Uo OLEH 489236 33397 4595402 32655 09 PIME 491264 64887 4599364 261589 10 RAIN 459646 50167 4597499 31361 11 SICK 456465 77525 4601083 62
22. Save your file 17 STEP BY STEP How to Set Up D2M for Your Project You have now finished customizing your parameter information for your D2M file Supplemental Guidance on parameters Adding new Parameters If you are adding new parameters to the work sheet in PARAM INFO add the new Parameters at the end Re sort the parameters as described in Steps 7 and 8 in this Chapter Copy each new param and paste in the DATA ENTRY sheet Paste it in the first empty cell in row 7 to the right of all other parameters Otherwise it will copy over your existing parameters Finally you will need to also follow the steps described on Page 31 for redefining the excel name for param list so that the range of colums includes the newly added parameters Deleting old parameter In PARAM INFO Delete the Parameter and do the same in DATA ENTRY Sheet Finally you will need to also follow the steps described on Page 31 for redefining the excel name for param list so that the range of colums accurately includes the list of existing parameters STEP BY STEP How to Set Up D2M for Your Project 18 Enter Site Names with Latitude Longitudes In this section we will replace the sample locations in the template with the actual monitoring locations in your project 1 Select the ADMIN MAP worksheet 2 In Column E paste or type in your monitoring site names under Site ID Site IDs can be any combination of letters numbers or sym
23. arts worksheet In this section you will make sure there is one chart and one data filter set up for each of your project s sampling locations 1 Select the Charts worksheet You will see the Charts worksheet which is set up with one data filter and one chart for each of the 16 Site IDs 1 2 3 etc that came with the template J sB o c D E F ell i 2006 Sampling Data for DO Standard 4 mg l last undated Judy 09 2007 10 42 AM Y axis units mg l crt crti Total phosphorus Site ID Year DO Site ID Ye Total suspended solids j 3006 lt gt HA 7 3 1 sample not in compliance 1 sample not D bi oo E in M d oo D M wp nm H TA 200E 62006 TA 5 2006 22 2006 T2 M2006 a 5 2006 a 22006 aA 92006 6 26 2006 9 2 2006 TA 2006 5 2006 TA S2006 a ia J i avg 7 35 Each time a query is run each data filter pulls out data for a site that matches the selected year and parameter For example the red box in the picture above shows the data filter for site 1 and is currently querying for 2006 DO data The query results are charted directly underneath You will modify these data filters so that the Site IDs reflect the monitoring site names of your project After that if you have less than 16 sites you will delete the extra data filters charts If you have more than 16 sites you will have to copy and paste to create additional data filters charts STEP BY STE
24. ba fon y ames lana d o Nanded F 2 EEI 13 8 Go to Data gt Sort and sort by ascending PARAM Column A Click OK Sort by ARAM Ascending Descending Then by Descending Then by SO cei Descending My data range has Header row O No header row You also need to enter your parameter into the column heading of the Data Entry To save you time from typing them in again we will simply copy your parameters from the Param Info worksheet and paste them into the Data Entry worksheet 9 Select the cells in Column A of the Param Info worksheet that contain all your parameters B To entev a tine break click ALT ENTER l Mops il ehon STANDARD atencaratereshnd anarad aata entered with the folowing excsptions TIF PARAM fe Dissolved P maps wil show THRESHOLD stansandtorestiosy entered and data below threshold wil be considered out of mpiancte 2 If UNTS is blank e g pH maps wil show STANDARD ctandardtavrestoid entered STANDARD UNITS Hon cormaliant item THRESHOLD label for y axes COMMENTS atandardi 051 Dissolved Phosphorus Summary Anal gt Dissolved Pis not a Stee stendard Rether ihe threshold of 51 dimsotved Pis used as an ndeeor of possible sepie rasla probleme Further tasting may be needed cince in core cases the Gecclved P exceads 100 OS mgt Amenorin Anehysin gt One key thing to note i thet the smania stendard i actually a ra
25. back layer in order for you to be able to select the individual site charts 9 1 Select the Sampling Point Chart Area 9 2 Right click and select Send to back 9 3 Start at Step 1 on pg 28 for charts in this Map Option The only difference is that for simplicity sake we named the sites by number only instead of Site XX You can name the sites in anyway you choose 9 4 Save your file STEP BY STEP How to Set Up D2M for Your Project 30 Re Define Excel Names In previous sections you modified your D2M template so that the chemistry parameters and site names are appropriate for your project In this section we will update the pre defined Excel names in D2M to reflect the changes you made to the chemistry parameters and site names 1 Ifyou are not currently on a Map Option worksheet go to one by clicking on any of the Map Option tab This section can be done from any of the Map Option worksheets MAP OPTION 1 MAP OPTION 2 MAP OPTION 3 2 Inthe Insert menu point to Name and then click Define Format Tools Data Window Help ee a ee Y create eria ductivity Apply Label You will see the Define Name dialog box 3 31 Define Name Names in workbook param_list map2_page map3_page other_map aram L param _Ikup Param_page param_temp Print_Area MAP OPTION 2 rangei vi Refers to Param Info l A 4 4 16 In this step we re define
26. been customized a basic level Excel user can maintain the application by simply pasting in the monitoring data The data can then be queried by parameter and by year and D2M will display the results as Charts Maps Maps images provide geographic context while Histograms of data along with standard threshold line to show Numerical Summaries Simple statistics and list of individual data points color coded sampling locations show how often a standard has been exceeded a o 6000 i 5000 s HT ra 4000 3000 data pts 235 cfuM00 ml ep E 2000 24 l 1000 frequency and extent of by sampling location exceedances e Year Date E coli 2004 9 14 2004 360 2005 6 21 2005 700 2005 6 22 2005 0 2005 6 23 2005 600 t DW Q Q A O wo wb The outputs can be printed directly from D2M or pasted into other applications e g PowerPoint MSWord for outreach and reporting materials Because the set up time is directly related to the number of sampling sites D2M is best suited for projects with pre determined and limited less than 15 sampling locations While D2M mimics some aspects of a GIS it is not a true GIS because the maps are not georeferenced For capabilities beyond those currently built into D2M a user would still need GIS Overview of Data2Maps This section gives an overview of how Data2Maps is set up We highly recommend you read this before opening the file Note If yo
27. bols Blanks are allowed between words Try to keep your site names short for ease of display and data entry NOTE You do not need to make changes in the Crit Num column right now SS SES eS SS as 3 In Columns F and G paste or type in the associated x and y coordinates x longitude easting y latitude northing LONGITUDE LATITUDE CritNum Site ID x Y f EXCEED 1 488456 93125 459081716004 O 2 489454 00422 4590849260806 O 3 49082510278 459082236706 O 4 493721 69402 4550505 06406 0 5 493914 08612 4568665 4067 O B 49470315399 458968800564 O Fi 492991 19339 4591661 57514 0 B 4589235 33397 4595402326853 9999 9 401264 84887 4599384 26189 D 10 AS96465 50187 4597499 31061 D 11 486465 77525 4601083 62632 O 12 486634 70135 460271116222 O 13 491395 03641 4600937 86278 O 14 489250 62189 4603840 38456 9999 15 488164 87760 4594133 89849 9999 16 487847 10733 4605335 58082 9999 19 STEP BY STEP How to Set Up D2M for Your Project mapdata LONGITUDE LATITUDE Crit Num x Y EXCEED 1 468456 93125 4590817 18004 o 2 2 489454 00422 4590849 23806 o J 3 490825 10278 4590822 36706 o 4 4 493721 69482 45686583 56408 0 5 5 493914 06612 4558865 24067 i G 6 494703 15399 4599683 00564 o T Y 49299119339 4591681 57514 o g O 469236 33397 4595402 32653 9999 g 491254 84687 459938428189 o 10 489646 50167 459749931861 o 11 466465 77525 4601083 62632 o 12 45663470135 460271116222 o 13 491395 03641 460093
28. d hit your Delete key 1 3 Repeat with all extra charts In this step we will rename the charts if your sites are differently named than the ones in the template To know which site a chart is associated with go to the Charts worksheet A chart labeled Site X on the Map Option worksheet corresponds to the site listed under critXX on the Charts worksheet For example in the template the chart listed as Site 1 on a Map Option worksheet Site 1 corresponds to the site listed under crit01 on the Charts worksheet simply 1 in this example 3 29 eee E 2006 Sampling Data for DO Standard 4 mg l last updated July 09 2007 10d AM Y axis units mg l Site ID Year DO 2006 lt gt HA 1 sample not in compliance Oo on oo 4 oo This step is not necessary for Map Option 2 since its embedded charts are not labeled only the sites are For each chart 3 1 Select the Site Name by clicking on it 3 2 Type in the site name that corresponds to it in the CHARTS worksheet You can choose NOT to include the word Site Move charts so that they are well arranged in the black bordered print area Note You may want to wait until your points are mapped in Step 8 since we recommend moving charts as close as possible to the corresponding sampling point on the map STEP BY STEP How to Set Up D2M for Your Project 5 Save your file 6 Skip to the next section Re Define Excel Names on
29. dates of your query results 41 STEP BY STEP How to Set Up D2M for Your Project Align Maps with Sampling Locations It s finally time to map out your sampling locations Here s what you will do to make it happen Enter fake data Look at your sampling locations Insert your map s Align your sampling locations with Map1 Position your legend Label the sampling locations Position your charts Repeat for other layouts 1 e Map Options if any Enter fake data In order for us to see our sampling locations we have to first enter fake data in the DATA ENTRY worksheet for every one of your locations We will do this by copying all the sites you entered in the ADMIN MAP worksheet into the DATA ENTRY worksheet and entering the fake data 1 Goto the ADMIN MAP worksheet 2 In Column E select and copy all your site names cael mapdata Site ID Site x Yy EXCEED 1 488456 93125 4590817 18004 0 2 489454 00422 4590849 28806 0 3 490825 10278 4590822 36706 0 4 493721 69482 45885983 86408 0 5 493914 08612 4588865 24067 0 6 494703 15399 4589688 00564 0 7 492991 19339 4591681 57514 0 8 499238 33397 459540232653 9999 9 491284 84887 4599384 28189 0 10 489646 50167 4597499 31861 0 11 488465 77525 4601083 62632 0 12 486634 70135 4602711 16222 0 13 491395 03641 4600937 86278 0 14 489250 62189 460384038456 9999 15 488184 87760 4594133 89849 9999 16 J 487847 10733 4605335 58882 9999 3 Goto the DATA ENTRY wo
30. e formula bar to see the formula for this cell co cP ca XCR es er ot cul cv cri Year Ammonia nitrogen Site ID Year Ammonia nitr lt gt HA E16 1 0 lt gt HA Ho sample data This was originally the site name in the ADMIN MAP worksheet associated with crit16 originally called 16 so this formula shows e 6 8 7 In this cell retype the formula with your 17th sitename so that it looks like ft sitename where sitename is the site you listed on the ADMIN MAP worksheet in column E next to critl7 NOTE It must be typed identical to the listing on the ADMIN MAP worksheet in order for the Refresh Charts Maps button to work correctly so you may way to copy and paste the name into the CHARTS worksheet Next we will redefine the chart for this site so that it is pointing at the correct data columns in the CHARTS worksheet 8 8 Right click on the chart under the data filter you just set up and select Source Data 8 9 Click on the Series tab STEP BY STEP How to Set Up D2M for Your Project 26 8 10 In the Values box change both occurrences of CR to the column letters of your new parameter info in this case you would enter CW i CT CU Cy CX CY CZ DA DB Source Data Site Year Bacteria 2 2 2 ss JP NOON 17 lt HA lt gt HA 250 x 200 2 150 3 A 100 4 50 2 0 z 3 wo o o o L S rd E Fa a i o oa
31. e page breaks 26 If the preview is acceptable go to File gt Print Otherwise first follow the instructions below also found in Microsoft Excel Help on how to adjust page breaks Microsoft Excel Help Adjust page breaks before printing P Show All To print the exact amount of pages you want you can use the Page Break Preview view to quickly adjust page breaks In this view manually inserted page breaks appear as solic lines Dashed lines indicate where Microsoft Excel will break pages automatically 1 On the View menu click Page Break Preview Do one of the following e To move a page break drag the page break to a new location Note Moving an automatic page break changes it to a manual page break e Toinsert a vertical or horizontal page break select a row or column below or to the right of where you want to insert the page break right click and then click Insert Page Break e Toremove a page break drag the page break outside of the page break preview area e Toremove all manual page breaks right click any cell on the worksheet and then click Reset All Page Breaks STEP BY STEP How to Use Data2Maps 12 STEP BY STEP How to Set Up D2M for Your Project This chapter walks you through how to customize D2M for your particular project by following these eight steps l 2 Tr 8 Enter Parameter Information Enter Site Names with Latitude Longitudes Set Up Data Filters Set Up Embedded Charts
32. ese macros to run and will prevent the system from working 1 In Excel go to Tools select Macro then select Security Data FlashPaper Window Help Adobe POF Spelling 100 Research Alt Click l jl mra a a A Error Checking Speech P Macros Alt F8 Add Ins Record New Macro umore epee Aueen visual Basic Editor Alt F11 Options gt Microsoft Script Editor Alk Shift Fi1 Custom Delimited File 2 Make sure medium is selected and click OK Security Security Level Trusted Publishers very High Only macros installed in trusted locations will be allowed to run All other signed and unsigned macros are disabled High Only signed macros From trusted sources will be allowed to run Unsigned macros are automatically disabled Low not recommended You are not protected from potentially unsafe macros Use this setting only if you have virus scanning software installed or vou have checked the safety of all documents YOU Open 3 Ifyou changed the security setting you will need to close your Excel application and reopen it for the security level to be applied 4 Go to File gt Open and navigate to your customized D2M file If it has not been created yet you can use the D2M template D2M_template xls 5 STEP BY STEP How to Use Data2Maps Entering Data All your data is entered in the Data Entry worksheet If your D2M file has been customized
33. for your project the column headings in this worksheet will be labeled with your sampling parameters 5 Select the Data Entry worksheet by clicking on the DATA ENTRY tab at the bottom of the window ar MH oao KO DATA ENTRY MAP OPTION 1 MAP OPTION 2 MAP OPTION 3 CHARTS Insti lt 6 The Data Entry sheet is now active E Microsoft Excel D2M_template_v xls Format Joos Qata PlachPaper Window Help Adobe PDF A 8 1 DATA ENTRY SHEET 2 Once the sheet has been customized for your project you can enter data starting in row 0 3 Dona change the column headers afer customization or queries wil not work mg mg Total phosphorus Total sunpendied sotte 7 12 2 16 018 097 5 Sat Creek Headwaters se 2205 Sa 7 0 08 6 Sager s Lake utet 765 76 7 Ueeuty Creek 8 Peeper Crock 9 Unnamed Triutery Materds Landing 10 Aiert Springs Outiet 14 Cirt Oech 15 Sal Cronk Headwaters 16 Booty Creek 8 Peeper Croci 9 Unnamed Trixtary Materds Landing 10 Aiert Sorings Outiet base 13 Damen Run bese 14 Gustafeocn Dach bate d Pow id gt W DATA ENTRY MAP CEPTION 1 MAP OPTION 2 MAP COTION 3 HARTS irst ioum gt lagos a Oai TE we O E ur 7 If any fake data still exists from the original template select and delete 8 Enter your sampling data All sampling data for a given site and date are entered in one row You can enter up to 400 rows of data The fields in the worksheet are listed be
34. g charts so that they match the number of your sites and 2 For Map Option 1 re labeling the charts with your site names If you have customizing Map Option 2 skip to the next section Re Define Excel Names on page 31 You will return this after you have finished setting up the maps so that you can align the charts next to the sampling locations If you have determined that you will be using only Map Option 3 which has no embedded charts skip to the next section Re Define Excel Names on page 31 Map Option 1 Worksheet If you have exactly 16 sites go to step 2 on pg 29 to rename charts If you have more than 16 sites go to step 7 on pg 30 to add charts If you have less than 16 sites follow the following directions to delete the extra charts from the template 1 e those in the template with Site ID greater than your number of charts 1 To delete extra charts do the following 1 1 Select the chart by clicking just outside the graph Leave your mouse over the selection for one second and a tool tip will pop up telling you what has been selected Make sure you have the Chart Area selected not the Plot Area YES Chart Area is selected Flot Sree STEP BY STEP How to Set Up D2M for Your Project 28 This might require a little practice getting used to what chart element you are selecting This aspect of Excel can be frustrating even when you get the hang of it 1 2 With the Chart Area selecte
35. g it to the front 5 saltcreek 311_template 06062007 xls Module6 Code Sele i Dee ar ato vi saltcreek 311_template 06062007 xls Sheet10 Code Selle r Pener z iDectarations ae 37 STEP BY STEP How to Set Up D2M for Your Project 4 About halfway down the page you will see a section that starts with Charts Filter data see following picture False Transpose False Range year temp select 2electian cCopy Range year Select eelection Pastespecial Paste xlValues Operation xlNone Akipblanks _ False Transpose False CHARTS Filter deta a sheets CHARTS Select 2hHeets CHARTS DisplayPaqeBreaks False Range data AdvwancedFilter Action xlFilterCopy CriteriaRkange Rangel _ eritoi CopyToRange Range rangei Unique False Range datat AdvancedFPilter Action x1FilterCopy CriteriaRkange Range _ erithe CopyToRange Range rangel Unique False Range datat ddvancedFPilter Action x1lFilterCopy Criteriakange Rangel _ T That section contains code for 16 sampling locations We will change that code to match the number of sampling locations in your project 5 If you have less than 16 sampling locations 5 1 Delete the pairs of lines starting with Range data that include critXX numbers where XX is greater than your number of sampling locations For example if you only had 14 sites you would select and delete the 4 line
36. ine Minimum Maximum Dates for Cha rts cc cc ccseeeseeseseeeecceeeeeeeeeaeaaeeeeseeeeees 40 Align Maps with Sampling LOCA ONS wicivejeccoedaedenuustien setastanwbvodeiarstacdvcesretevshotavtanetx 42 Appendix A Troubleshooting and Tips cccceessessssssseeeeeeecccceeeesasaeeeeaessssseseeeeeeeeeeees 55 Appendix B Creating Maps for Data2Maps cccccccceeccccceeeeeeeeecaaeeseeesssseeseeeeeeeeeees 5 Introduction Locally led watershed projects need to be able to demonstrate what the water quality data of their local area is in order to attract support Being able to show people where the problems are in the watershed is an important tool for building public support Smaller organizations tend to be more focused on implementation and may not have the available personnel expertise or technology to pull together sampling results in a way to clearly demonstrate the water quality of their watershed To address this need working with local watershed groups USEPA Region 5 developed Data2Maps D2M D2M is a custom Excel application in which users can overlay their sampling data on static maps and do preliminary assessment and analyses By using Excel for this template it allows local organizations without many IT resources to assess their data and present it in a visually engaging manner D2M requires a one time set up for a given project approximately 1 2 working days by an advanced Excel user Once the application has
37. ion This can be very helpful as a hardcopy handout at a meeting 1 Overview of Data2Maps Lake Michigan 4 All samples in compliance a 1 sample not in compliance gt 1 sample not in compliance No Data oS ee All samples in compliance 1 sample not in compliance gt 1 sample not in compliance No data Ton Th a gt Go 4 Plot Area P weve ss ae e Den a ga 5 r All samples in compliance All samples in compliance 1200 q 1200 MAP OPTION 2 Worksheet Charts are displayed directly on map next to the associated sampling location The charts show the standard threshold line but are stripped of labeling info These charts are only meant to give a rough sense of frequency and extent of exceedances In this example it shows that conductivity is significantly above the standard near sites 6 and 7 near a road salt storage area This layout is recommended if user has few sampling locations lt 10 Otherwise the map becomes hard to read MAP OPTION 3 Worksheet This simple layout does not include charts It is a good option for public meetings because it is the cleanest and easiest to understand We would recommend printing out charts see next worksheet to use in conjunction with this map so that you can provide more detailed information on each location 1000 o 1000 800 800 600 600 400 400 200 200 o o s amp amp amp amp Ri FS PF SF OS OS ee Pg e a n 4
38. is step we re define the name param_lkup which allows D2M to look up information associated with each parameter 4 1 Go to the Param Info worksheet 4 2 Open the Define Name dialog box Insert gt Name gt Define 4 3 Click on the name param_Ikup from the Names in workbook dropdown list 4 4 Click on the 8 button right side of the Refers to field 4 5 Select all the parameters you entered on the Param Info worksheet ALONG WITH ASSOCIATED FIELDS columns A E STANDARD UNITS Hon compliant if gt lt PARAM THRESHOLD __ labelfor y axes COMMENTS standard Dissolved P e home ee ee 4 6 Click on the button to return to the Define Name dialog box 4 7 Click on the Add button Do not forget this step otherwise no change will be made 4 8 Click on the Close button to close the dialog box 33 STEP BY STEP How to Set Up D2M for Your Project 5 Next we re define the name mapdata which allows D2M to categorize each location by frequency of exceedances 5 1 Go to the ADMIN MAP worksheet 5 2 Open the Define Name dialog box Insert gt Name gt Define 5 3 Click on the name mapdata from the Names in workbook dropdown list 5 4 Click on the button right side of the Refers to field 5 5 Select all the sites you entered on the ADMIN MAP along with the associated fields columns D H and column headings row 2 Crit Mum 2 3 4 5 z f
39. ll be given the names map1 map2 and map3 because you will be replacing the template maps with yours When a map is selected you can see its coded name in the name box in the upper left hand corner of the spreadsheet All Sampling Data for Total phosphorus 2C DO amp Site 16 E coli J Standard No state standard Flow o last updated July 10 2907 1 20 PM mIBI Nitrate Nitrite i l 16 All samples in compli As mentioned before the sampling location layer is simply an Excel scatterplot with latitude as the x axis and longitude as the y axis The sampling location layer is programmed to always be the topmost layer and 1s color coded based on frequency of exceedance The coded name for this layer is samp_data samp datalayer selected 45 STEP BY STEP How to Set Up D2M for Your Project Insert your map s We are going to swap out the map1 currently in the template with the map you have chosen for map 25 In the Map Option worksheet you have been working with select map1 by typing map1 in the upper left Name box and hit the Enter key You have to type in the name manually map names do not appear in the Names drop down list Notice that the map area is highlighted 26 Hit Delete to remove current map1 27 Go to Insert gt Picture gt From File and navigate to the map you have chosen for your map1 Click Insert 28 Move your map until it is approximately withi
40. low along with allowable values Date Monitoring date month day year Site ID Site ID as defined in set up process Site Name Full name of site Not required Does not appear in maps or charts Event Enter storm or base if known This column will be listed with the individual data points in the Charts worksheet Parameter Enter measured value Signs lt gt allowed but will be omitted in analysis No other non numeric values allowed e g ND Leave blank if no value for that site date and parameter Changing units listed above the parameter will not change units on the maps charts To change them in the maps and charts go into the Param Info worksheet see next section Note Site names and parameters are pre set in maps charts and queries during the set up process If you include any new site names or parameters in this data sheet they will not appear in the maps charts or queries 9 Save your file STEP BY STEP How to Use Data2Maps 6 Customizing Parameter Information LA DATA ENTRY ACPERAM INFO MAP OPTION 1 MAP OPTION 2 MAP OPTION 3 CHARTS f Instructions The Param Info worksheet was customized during the set up process and contains your list of parameters along with the associated fields defined below Param Parameter name DO NOT CHANGE This is how the parameter will be shown in the query tool list This is how the parameter must be listed in the data entry worksheet for the
41. lysis Summary goes 1000 mgl diss soll Sel Copy mary Analysis aly sis Summary Goes 4 mal aly Sis Paste Special case the standard is the the oxygen concentre 235 ctu D0 mi maai Delete that E coli is a concern Dissolved Phosphor reater than 51 is a possible indicator of ar Clear Contents s P be Format Cells R of Biotic Integrity Row Height jndicate Mitrate is well below the state standar Hide Unhide Analysis This ia where analysis summary goes Total suspended solids Mo state standard mal Total suspended Solids Analysis This is Where analysis summary goes Now that you are done entering parameter information we need to sort the parameters alphabetically so that the data query works correctly If you choose to enter no standard then please note that the dots on your map display will always appear as green regardless of the numeric value Also on chart displays the standard threshold bar will be set on 15 STEP BY STEP How to Set Up D2M for Your Project 7 Select all the parameters from column A to E as seen below _ Wears wil shor STANDARD siodarciecainky ehia avis caier wih ths folloyying eecsgtions TURP ARG wis Cissobeed Fi mape wil show THRESHOLE standardbiees tote entered Oh and chka below threshold wil be considered out of COT PGCE DFTE ehin isg pH maps wil show STAHCARD sisedardibesiodd entered STANDARDI UNITS Hem cornea nit if PARAMI THRESHOLD ba
42. n Excel as a backdrop for sampling locations Overview for GIS Analyst The requestor will be pasting the map s you produce into a D2M Excel file as a backdrop for sampling location points the sampling locations are displayed in the Excel file using a scatterplot of the x y coordinates with background no fill Excel Scatterplot Map Scatterplot on top of Map All samples in compliance 1 sample not in compliance gt 1 sample not in compliance a oY Appendix B Creating Maps for Data2Maps If the requestor asked for more than one map that means they will be setting up the D2M file so that they can choose the background map by a click of a button similar to Google maps o Al samples in pree mresa Pa MES po T 1 sample not in compliance walt id gt a X r ee he gt SHOW MAP All samples in compliance 1 sample not in compliance H e All samples i in compliance 1 sample not in compliance gt 1 sample not in compliance gt 1 sample not in compliance gt 1 sample not in compliance n amp No Data ve _ No Data No Data ee e7 PEIN is ies r A 1 amp oa Re TA a i c En d Ag R m an ul ours d a ui z x FAN S ta wr in fe G gt PAD Le e rs ttf i liverted mm sts 33 hee KU NE from Salt Ae ua iT ek P iK ER E u ra Be AS ri eas 4 W T PN t EAW M se alparai o i Menna EA Ak Tih ee Oe a gt a a ee e wre aot F ay
43. n the same area that the original map was Hint avoid resizing map images if possible that way you will not accidentally change the aspect ratio and you will not have to resize your other maps to match this one After all the images are in place you will adjust the sampling location layer samp data instead 29 Click on the map to make sure it s selected and then type in map1 in the upper left Name box Hit the Enter key and now your map is titled map1 30 Save your file Map is now correctly added to this worksheet E SHOW MAP SHOW IMAGE SHOW MUNICIP If you will be using more than one map we will now repeat the previous steps STEP BY STEP How to Set Up D2M for Your Project 46 31 32 33 34 If you have a map2 31 1 Repeat Steps 25 29 this time substituting map2 for map 31 2 Match the corners of map2 with the corners of map NOTE Remember the easiest way to select map2 is by entering map2 in the Name box Assuming that map2 was created with the same extent as map1 matching the corners of the two maps mean that once the samp data sampling points are aligned with map1 they will also be aligned with map2 If you have a map3 32 1 Repeat Steps 25 29 this time substituting map3 for map 32 2 Match the corners of map3 with the corners of map1 or map2 If you are not using all 3 maps we recommend leaving the template maps in there as a placeholder 33 1 T
44. ndar year instead of minimum maximum dates of query results See instructions at Appendix A Troubleshooting and Tips on pg 55 10 If you have exactly 16 sampling locations skip to the next section on pg 42 Align Maps with Sampling Locations 11 Go to the Admin Map worksheet 12 Select cell A11 In the formula bar above you will see the calculation of the minimum sampling date Notice how it refers to all the datesXX names you set up in the Re Define Excel Names section a start_date amp MIN dates01 datesO02 dates03 dates04 dates05 dates06 dates07 dates08 dates09 dates10 dates11 dates12 dates13 dates14 dates15 dates16 31 mapdata LONGITUDE LATITUDE No exceedances One excee Site ID X Y EXCEED mapcritt mapcrit2 1 1 488456 93125 4590817 18004 0 EXCEED EXCEED 2 2 489454 00422 4590849 28806 0 0 1 3 3 490825 10278 4590822 36706 0 4 4 493721 69482 4588583 86408 0 Site ID X Y Site ID 5 5 493914 08612 4588865 24067 0 1 488456 93125 4590817 18004 6 6 494703 15399 4589688 00564 0 2 489454 00422 4590849 28806 7 7 492991 19339 4591681 57514 0 3 490825 10278 4590822 36706 5 8 489238 33397 4595402 32653 9999 493721 69482 4588583 86408 g 9 491284 84887 4599384 28189 0 5 493914 08612 4588865 24067 10 10 48964650167 4597499 31861 0 6 494703 15399 4589688 00564 11 11 ARRARS 77595 ARMANA RIRI n 7 4q7qq1 19330 ASRR 57A 13 If you have less than 16 samples 13 1 Select cell A11
45. nge kased on Tertperature ond pH condlions a ihe ime of aneping Coneetrations from 0 5 2 mgl could be the threshold that would violate the standard The threshold in these grapire fee heen sel sf 0 5 moi for a Coneervalive review 23 ing Chiorid Sumenery Analysis This is where Analysis Summary goes 1000 mg dine selicie Comductivty Sumenery Analysis gt This ig where snstysizfsuinmary goes 3 mgl DO Sumenery Araiysis x Reminder N thie cese the standardis the the oxygen Concentration should be ef leset 4 mg L We wani to sae data skove the threshold inthis case 235 cfu 00 ml Ecok Summary Analysis gt his faryodviousthat E colis a concen Dissolved Phosphorous deta con hep refine this nfonnebon Orsseived phosphorous of gresler than 51 is a passive indicator of anihropogenic sources of e coi OS cts 22 Moecrokictic Index of Bistic Integrity 10 mgt Merate Anetysks Ed Arelysis resus indicate Nirate is wel helow the stele standard 7 gt 19 degC gt O stele stancaed ing Total Phosphorus Anehysix gt Thie ie where ah pyala aunmery goes o tme standard mol Total Suspended Seles Anatysis gt This ie where anslysisfounmary goes 10 Copy the selection CTRL C or Edit gt Copy STEP BY STEP How to Set Up D2M for Your Project 16 11 Go to the Data Entry worksheet click on yellow DATA ENTRY tab on bottom DATA ENTRY PARAM INFO MAP OPTION 1 MA You can see that in the Da
46. ngeXX values with the next consecutive numbers Range data idvancedFilter Action x1lFilterCopy Criteriakange Rangel Feritis CopyToRange Range rangele Unique False Range data dadvancedFilter Action xlFilterCopy CriteriaRbange Range _ eri i CopyToRange Range rangei Unique False Range data advancedFilter Action x1lFilterCopy Criteriakange Range _ ror ifs CopyToRange Range rangdia Unique False ADMIN MAP Filter data I 8 sheets ADMIN Map 5elect Hit Alt Q or go to File gt Close and return to close the Visual Basic Editor and return to your spreadsheet Save your file You are now done modifying the VBA code so that it refers to the correct number of monitoring sites that you are using 39 STEP BY STEP How to Set Up D2M for Your Project Define Minimum Maximum Dates for Charts On the ADMIN MAP worksheet there are 2 cells that define the minimum and maximum dates to display on every chart in your D2M file Every time you run a query those dates are refreshed to reflect the earliest and latest sampling dates of the query results The formulas for the minimum and maximum dates are currently set up forl6 sampling locations If you have a different number of sampling sites you will follow the steps in this section to reformulate those two cells to reflect your number of sampling locations NOTE You can also set up your charts to show full cale
47. non cell e g map chart button is selected Click on any cell and then try entering again Problem Sampling locations are always selected even though I am trying to click on another item Solution Sampling locations is the top layer and will always be selected when its area 1s clicked on Send the sampling locations to the back by typing samp data in the Names box right click and choose Send to Back When you want the sampling locations back in the front typing samp data in the Names box again right click and choose Bring to Front Problem I want the charts to show full calendar year s Solution You can change the charts to show the full calendar year by entering the following formulas in the ADMIN MAP worksheet In Cell A11 enter F year lt gt All DATE year 1 1 DATE MIN year_list 1 1 In Cell A12 enter IF year All DATE year 1 1 1 DATE MAX year_list 1 1 1 If you want the charts to show only the sampling date range again follow steps XXX to return to the original setting Maps Problem I want to crop my map Solution 1 Select the map by typing its name into the Name box 2 Goto Format gt Picture or hit CTRL 1 3 On the Picture tab enter approximate numbers into the Crop from Left Right Top Bottom boxes 4 Click OK 5 Repeat with different crop measurements until the map shows only the extent you are interested in NOTE You will have to enter the exact
48. ns Param Info Data MAP CPTION 1 MAP OPTION 2 MAP OPTION 3 CHARTS Z ADMINOM 4 The Param Info worksheet shows the following columns PARAM STANDARD THRESHOLD COMMENTS UNITS and Non compliant if gt lt standard A E c D E F Maps wil show STANDARD standardthreshold entered units entered with the following exceptions 1 To enter a line break click ALT ENTER STANDARD UNITS Hon compliant if gt lt THRESHOLD label for y axes COMMENTS standard Dissolved P is not a State standard Rather the threshold of 51 dissolved P is 4 used as an indicator of possible septic waste problems Further testing may be Ammonia nitrogen 0 5 mgA Ammonia Analysis gt One key thing to note is that the amonia standard is actually a range based on Temperature and pH conditions at the time of smapling Concetrations from 0 5 5 2 mg could be the threshold that would violate the standard The threshold in Bacteria 230 mgA Chloride Summary Analysis gt 6 This is where Analysis Summary goes Conductivity 1000 mgA diss solids Conductivity Summary Analysis gt Fa This is where analysis summary goes DO 4 mgl DO Summary Analysis lt Reminder In this case the standard is the the oxygen concentration should be 6 at least 4 mg L Ve want to see data above the threshold in this case E coli 235 cfuN00 ml E coli Summary Analysis gt STEP BY STEP How to Set Up D2M for Your Project 14 5 Enter the parameter informati
49. o be moved 38 2 Select map3 by entering map3 in the Name box 38 3 Resize move map3 until its edges line up with those of map 39 Save your file STEP BY STEP How to Set Up D2M for Your Project 50 Position your legend If the legend is covering sampling points or prominent features on your maps you will need to move it The legend is an element of the samp data chart 40 Click on the legend to select it You can move it any area to within the samp data chart area You can also resize it by selecting and dragging the handlebars 41 Save your file Label the sampling locations The sampling location labels are contained in a separate layer on the worksheet This layer is composed of text boxes that have been grouped together We will ungroup these labels rename them with your location names and move them to the appropriate positions 42 In order to select the labels you have to send the samp data chart to the back bottom layer 42 1 Select the sampling locations by typing samp data in the Names box Hit Enter 42 2 Right click and choose Order then Send to Back o Standard 235 Cfu 100 ml o jast undated tune IR 2007 LSRPNI Site 9 Se d3 Edit Chart Object Grouping Site 10 i Bring to Front Assign Macro aa Sends Back Set AutoShape Defaults a Uy Bring Forward Format Object LI Send Backward E coli Summary Analysis It is Fairl
50. o make the unused map non obtrusive select it using the Name box and then resize small enough so that it cannot be seen beyond the extent of your other map s 33 2 If you want to make the associated button look non active then right click on the button choose Format Auto Shape go to the Font tab and choose a color which matches the button color This way the button is still there as a placeholder but users are less likely to click on it 33 3 If you are ABSOLUTELY positive you will never use 3 map backgrounds contact Janice Huang 312 353 8228 for directions on how to remove maps and buttons without causing an error in the coding Save your file Your maps are now inserted in your Map Option and are ready to be aligned with your sampling locations 47 STEP BY STEP How to Set Up D2M for Your Project Align your sampling locations with Map1 This step 1s tricky and takes lots of patience The good news is that once your sampling locations are lined up with map1 you only have to match the corners of map2 and map3 to the corners of map1 for the sampling locations to be aligned to those maps too assuming your GIS person created all your maps with the same extent NOTE You will be moving and resizing the sampling locations chart samp data to align with the maps NOT the other way around If you have to resize your map make sure you drag from the corners so that the aspect ratio stays constant and your map will not
51. o not change the column headers after customization or queries will not work storny base dey C myi myi Date i Stream Name Event Temp pH Total phosphorus Total suspended solids E coli 84 2006 1 Lake Louise Outlet hase 18 12 77 0 2 16 1900 84 2006 2 Clark Ditch hase 26 67 77 0 18 22 360 0 8 2006 3 Unnamed Tributary Veblos base 27 55 7 2 0 97 9 500 84 2006 4 Block Ditch hase 18 54 7 2 0 3 190 8A 2006 5 Salt Creek Headwaters base 22 05 74 0 08 29 220 84 2006 6 Sager s Lake Outlet hase 24 87 7 6 0 14 310 8 1 2006 7 Beauty Creek base 16 56 i 7 2 0 100 8 2 2006 8 Pepper Creek base 40 8 2 2006 9 Unnamed Tributary Mallards Landing base 24 91 78 100 8 2 2006 10 Butternut Springs Outlet base 19 53 8 1 0 8 8 2006 11 Squirrel Creek base 16 8 i tt 8 8 2006 12 Robbin s Ditch hase 20 2 8 1 0 12 8 8 2006 13 Damon Run base 25 93 ti 8 22 2006 14 Clark Ditch hase 6 22 2006 15 Salt Creek Headwaters hase 6 22 2006 16 Beauty Creek base 6 22 2006 3 Pepper Creek base 6 22 2006 9 Unnamed Tributary Mallards Landing base 8 22 2006 10 Butternut Springs Outlet base 6 22 2006 13 Damon Run hase 6 22 2006 14 Gustafson Ditch hase 4 gt ADATA ENTRY MAP OPTION 1 MAP OPTION 2 f MAP OPTION 3 f CHARTS Instru lt This spreadsheet was not designed to be a database for storing sampling data and only contains information necessary for running D2M Because it does not include fields for quality assurance or other metadata you should use a separate da
52. on Siep 3 Choose one sampling year Al Cross your fingers Step 4 Click Refresh Chars Maps REFRESH CHER TS MAPS The map should now be updated with your sampling locations Confirm that you see the correct number of sampling points and that they are in approximately the correct relationship to each other although they will still need to be moved and the extent resized in order to match your maps If the orientation is not correct make sure your points have their latitude longitudes entered in the correct order in the ADMIN MAP worksheet see Step 3 on pg 19 Save your file NOTE In all the Map Options the sampling locations are shown using scatterplot charts with latitude as the x axis and longitude as the y axis In each Map Option you plan to use we will align these scatterplot charts with your underlying maps STEP BY STEP How to Set Up D2M for Your Project 44 A quick orientation of D2M maps You can choose up to 3 maps to display under the sampling locations In the template the 3 maps are 1 a watershed map 2 an aerial photo and 3 a map with municipal boundaries SHOW MAP SHOW MUNICIP The buttons on the template use the labels MAP IMAGE and MUNICIP and allow you to decide which map should be the top visible map layer the other maps are hidden beneath In the Excel code these maps are referred to as map1 map2 and map3 respectively NOTE Decide which of your maps wi
53. on in the columns as defined below You can add up to 22 parameters Param Standard Threshold Units Comments Non Compliant if gt lt standard Parameter name There is no maximum character length but try to keep shorter for readability in the query list e Do not use identical parameter names the program will only use the first one 1t comes across Enter a numerical value in this column if you want to track your data against a state standard or other threshold e Ifnone entered enter No standard e If param is Dissolved P maps will use the label Threshold For all other parameters maps will use the label Standard e Cannot be a range Units for data and standard threshold must be same for both Comments for a given parameter if any that you would like displayed under the maps when a particular parameter is chosen e Can be entered now or later e To enter a line break hit ALT ENTER e Do not format bold italicize text in this cell because formatting will not show up on maps Distinguishes if a parameter is noncompliant if it falls above or below a threshold Enter gt or lt 6 After you have added your parameters if there are still parameters from the original template that you will not be using select those rows right click and select Clear Contents or Delete raps Nas peen Sel al UD mg Tor a CONnservalye revi 230 mad Analysis j a
54. pg 31 If you have more than 16 sites you will need to make additional charts 7 For each new chart 7 1 Select one of the charts on the Map Option 1 worksheet remember to select the Chart Area 7 2 Right click and choose Copy 7 3 Click on an empty cell for example on the top row so that your chart is no longer selected 7 4 Right click and choose Paste Note If your data is being pasted again into the original chart instead of in a new chart it means you did not unselect your chart in step 7 3 7 5 Point the chart at the correct data by doing steps 8 8 8 11 pg 26 on this chart 7 6 Rename the chart see step 3 1 on pg 29 We will not worry about putting the charts in a particular order until we have added the map s and sampling locations 8 Save your file You have now updated your maps to include one chart for each of your monitoring sites for Map Option 1 Map Option 2 worksheet Since the charts in Map Option 2 need to be positioned next to the sampling locations we will wait until after you insert and align the maps before we update the charts So for now continue with the next section Re Define Excel Names on pg 31 AFTER YOU HAVE FINISHED THE REST OF THE SET UP PROCESS The charts in Map Option 2 are layered on top of the map s Now that you have lined up your sampling points with your maps you can align your charts to match 9 The overall sampling points chart has to be sent to the
55. pling locations If the map is no longer visible do this step again carefully YES Map still visible NO Map no longer visible handles are hollow circles handles are black squares All Sampling Data for DO 2006 All Sampling Data for DO 2006 a z Standard 5 mg l 2 last updated June 27 2007 3 44 PM ite 16 All samples in compliance All samples in compliance 4 1 sample not in compliance 1 sample not in compliance 1 P gt 1 sample not in compliance gt 1 sample not in compliance e 12 No Data No Data 11 e9 Ammonia Analysis One key thing to note is that the amonia standard is o actually a range based on f mperature and pH conditions a actually a range based on Temperature and pH conditions 36 2 Resize and move the sampling locations chart until its sampling points are aligned with the corresponding sampling points on the map Remember to focus on sampling points on opposite ends of your extent When those look correctly aligned they you can confirm that the points in between are aligned too e To resize the chart click on a handle and drag e To move the chart click in the middle of the chart double arrows appear and drag Depending how far off your sampling points are you may have to adjust where the samp data s plot area is positioned within its chart area All Sampling Data for E coli 2006 AllsamplesincomDliance 1sample not in co pliance i gt 1 sample no
56. query tool to work Standard User defined standard threshold Threshold e Ifnone No state standard should be entered e If param is Dissolved P maps will use the label Threshold For all other parameters maps will use the label Standard e Cannot be a range Units Units for data and standard threshold must be same Comments Comments for a given parameter if any Displayed under map when appropriate parameter chosen e To enter a line break click ALT ENTER e Do not format this cell because formatting will not show up on maps Non Compliant Enter gt or lt to define non compliance with the standard threshold if gt lt standard e Example for dissolved oxygen you would enter lt e Example for E coli you would enter gt You can modify any of the associated fields The parameter name itself has to be done through the set up process on pg 14 under Enter Parameter Information 10 Select the Param Info worksheet by clicking on the PARAM INFO tab at the bottom of the window 11 Modify the information in the appropriate cell 12 Save your file 7 STEP BY STEP How to Use Data2Maps Querying Data You can query your data from any of these worksheets Map Option 1 Map Option 2 Map Option 3 or Charts When you run a query from any of the map chart worksheets all the map chart worksheets are updated with the query results 13 Make one of the Map Option
57. r file Position your charts We have put off positioning your charts until now because it is best to place them near the actual sampling points If you are working with Map Option 1 we recommend you order your charts they are as close as possible and similar orientation to the associated sampling locations on the map 50 Select each chart and position them in an order that mimics sampling location orientation on map NOTE Make sure the Chart Area is selected before moving chart Directions in Step 1 1 pg 28 51 Save your file NOTE If you are working with Map Option 2 you will now return to the section Set Up Embedded Charts in MAP OPTIONS 1 and 2 on pg 28 to set up your charts and then position them on the map next to the appropriate sampling location You are done customizing Map Option 1 53 STEP BY STEP How to Set Up D2M for Your Project Other Map Option worksheets If you are customizing Map Options 2 or 3 you will now do all the steps in this set up section except for any differences specific to Map Options 2 or 3 noted in each section You are now ready to start using this application Enter data into the DATA ENTRY worksheet Choose the parameter and year on the map options sheet s you customized Voila STEP BY STEP How to Set Up D2M for Your Project 54 Appendix A Troubleshooting and Tips Excel Names Problem I am not allowed to enter text in the Names box Charts Solution Most likely a
58. rksheet and select cell B8 the first cell under the Site ID heading NOTE Even though you are only selecting one cell when you paste in your selection it will extend across the correct number of cells 4 5 5 f Date Site ID Site Hame im B41 2006 Take Louise Outlet g aA 2006 2 Clark Ditch 4 Paste in your selection STEP BY STEP How to Set Up D2M for Your Project 42 15 16 we 18 43 Make sure that all your sites have a date in column A and these dates all have the same year NOTE These are sample dates to help set up the sites they do not have to be actual sampling dates Make sure that for each parameter there is at least ONE sampling value entered Fake data is fine this allows us to see all sampling locations when we align the sites to the maps in Step 8 Delete any extra rows of data that came with the template Save your file STEP BY STEP How to Set Up D2M for Your Project Look at your sampling locations We are now ready to query our fake data and look at our sampling locations to make sure they were properly entered 19 20 21 22 23 24 Go to the Map Option worksheet you plan to work with Stap 1 Choose one parameter In the blue query section choose one parameter in Step 1 and then click the UPDATE YEAR LIST button Step 2 Click Update Year List Choose a sampling year in Step 3 and then click the REFRESH MAPS CHARTS butt
59. rn fad ene ts Ce sa ie 2 ee ak lt uA A A Ee L S Mii WVE G Directions 1 Create a GIS view with sampling locations layer and map layer s asked for by requestor 2 Make sampling points small maybe 4 pt since they will need to be totally covered by another set of markers in Excel 3 If one of your maps is an aerial photograph make it slightly transparent you can try 40 transparency as a starting point so that the sampling points will stand out more 4 Zoom to the extent desired by the user most likely the extent of the sampling locations or the extent of the pertinent watershed You will now export each of the layers as GIS files Microsoft products make JPGs fuzzy because they have built in anti aliasing see http pptfaq com FAQ00065 htm In our experience it seems like images remain clearer when brought in as GIFs 5 The following export instructions are specific to ArcMap If you are using another GIS software package read through the instructions to see what end results you want in your GIS a Make the layer you want to export visible along with the sampling locations layer All other layers should be turned off NOTE The sampling locations layers should not be labeled We have found it s easier to control the display of the site labels if we add them in Excel b Go to File gt Export Map c Change Save as Type to GIF gif Appendix B Creating Maps for Data2Maps 58 59 d Inthe
60. s highlighted below because they have critXX where XX is greater than 14 Range data advancedFilter Action x1lFilterCopy CriteriaRange Rangel eritid CopyToRange Range rangel4 Unique False 5 2 Skip to step 7 6 If you have greater than 16 sampling locations 6 1 Copy the last 2 lines of that section Range data dadvancedFPilter Action x1lFilterCopy CriteriaRkange Rangel Peritis CopyToRange Range rangelsS Unique False Range erence AdwancedFilter ActionisxlFilterCcopy CriteriaRange Range Foeriti s CapyToRange Range rangei Unique False ADMIN Map Filter data Sheets A MIN MAP 5elect STEP BY STEP How to Set Up D2M for Your Project 38 6 2 Put your cursor on the beginning of the next line and paste in the selection once for each site you have beyond 16 For example if you have 18 sites you would paste in the selection 2 times Range data AdvancedFilter Action xlFilterCopy CriteriaRange Range _ oritie CopylTloRange Range rangel Unique False Range data aAdvancedFilter Action x1FilterCopy CriteriaRkange Rangel _ eritie CopyToRange Range rangelt Unique False Range data aAdvancedFPilter Action x1lFilterCopy CriteriaRkange Rangel _ eritie CopyToRange Range rangel6 Unique False ADMIN MAP Filter data oheets i ADMIN MAP 5elect 6 3 Replace the XX in the critXX and ra
61. same numbers into the Crop fields for other maps in that layout so that maps are correctly aligned Problem It s hard to see the sampling points on top of my map 55 Solution Tone down your map by selecting it right clicking and choosing Format Picture Under Picture tab pump up brightness try 65 and tone down contrast try 35 Appendix A Troubleshooting and Tips Problem Sampling points are not oriented in the correct positions Solution Make sure your points have their latitude longitudes entered in the correct order in the ADMIN MAP worksheet See Step 3 on pg 19 Problem Instead of a single threshold value one of my parameters has an acceptable range Known Issue D2M currently cannot handle comparing values to an acceptable range Problem I want to change the text on the SHOW MAP IMAGE MUNICIP buttons Solution Right click on the button and choose Edit Text The cursor will show up at the beginning of the button text and you can type in your changes Appendix A Troubleshooting and Tips 56 Appendix B Creating Maps for Data2Maps What you should have received from the requestor Lat longs for sampling locations along with site names Type of map s desired watershed delineation aerial image etc Which D2M layout or Map Option requestor will be using this will affects max width height of maps What you will be producing for requestor Map s exported as GIFs that will be used i
62. se no change will be made 7 7 Repeat Steps 7 1 7 6 for each site you added beyond the first 16 that came with the template that is critl7 and greater 8 Save your file You have now updated and added all the Excel names necessary for running the D2M query tool STEP BY STEP How to Set Up D2M for Your Project 36 Modify VBA Code The Refresh Maps Charts buttons are currently set up to query for the 16 sampling locations included in the template If you have a different number of sampling locations you will need to modify the VBA code behind this button Even if you ve never touched Excel VBA code before do not be afraid It s actually quite simple just follow these directions If you have exactly 16 sampling locations you can skip ahead 2 sections to Align Maps with Sampling Locations on pg 42 1 Goto Tools gt Macro gt Visual Basic Editor 2 On the left tree structure double click the Modules folder and then double click on Module6 wa Microsoft Visual Basic saltcreek 311_template 042 ii File Edit View Insert Format Debug Run Tools 70 ha IA TTA Project BAProject x General EAProject CustomDelimiterExpo Sub upde l BA EAProject saltcreek 311_templa 9 Microsoft Excel Objects i Modules update Macra l Hol Daim 3 Ifa blank window is on top see following picture then look for window titled Module 6 and click on it to brin
63. se the standard is the the oxygen cor H the threshold in this case E coli 235 cfuN00 ml E coli Summary Analysis tis fairly obvious that E coli is a concern Dissolved Phe phosphoruous of greater than 51 is a possible indicato Flow j 0 5 cfs miBI i 2 2 Macrobiotic Index of Biotic Integrity Nitrate Nitrite 10 mg Nitrate Analysis Analysis results indicate Nitrate is well below the state si pH 7 Temp 19 deg C Total phosphorus No state standard mg Total Phosphorus Analysis This is where analysis summary goes Total suspended solids iNo state standard mgA Total Suspended Solids Analysis i This is where analysis summary goes Define Name Mames in workbook param isk param list param lkup Param page param benp Print_Area MAP OPTION 1 range rangel rangel rangel4 ranges Refers to 3 6 Click on the Close button to close the dialog box and return to the Map Option worksheet STEP BY STEP How to Set Up D2M for Your Project 32 3 7 Check the parameter list on the top left of the Map Option worksheet It should now show your parameters You may have to scroll down the list to see all your parameters Mitrate Mitrite Ti 200G d Site 41 UPDATE YEAR LIST 0 TH 2006 If you can see your parameters you have successfully re defined the name param list In the following steps we will redefine more names Refer to the figures in Step 1 3 if you have questions 4 In th
64. sheet select all the cells or click the chart or object you want to copy To select an area click on the sheet and hold the mouse down dragging it across the area you want to capture see picture what is highlighted in blue was selected SHOW MAP ES IMAGE SHOW MUNICIP p All Sampling Data for DO 2006 i itis Standard 0 5 mg l Site 14 6 Site 15 Site 10 Site T Site 6 Ammonia Analysis One key thing to note is that the amonia standard is actually a range based on Temperature and pH conditions at the time of smapling Concetrations from 0 5 2 mg l could be the threshold that would violate the standard The threshold in these graphs has been set at 0 5 mg l for a conservative review Site 4 Y Axis unite mgl 19 Hold down SHIFT and click Copy Picture on the Edit menu EJ Microsoft Excel D2M_template_v x Edit View Insert Format Tool w Unde Zoom Ctrl z W Repeat Macros Ctri Y de Cut Ctrl Copy Picture k a Office Clipboard Paste Picture Paste Picture Link Paste as Hyperlink 20 For best picture quality make sure As shown on screen and Picture are selected and then click OK Copy Picture Appearance 4s shown when printed Format Picture C Bitmap STEP BY STEP How to Use Data2Maps 10 11 21 Go to the document where you are pasting into the picture below shows Microsoft Word and click where you want to paste the picture
65. t in c pliance No Data Phnsnhnaraus data ran heln refine this information You can do this by selecting the plot area click next to but not on a sampling location in samp_ data and following the moving resizing directions given in Step 36 2 NOTE The map 49 STEP BY STEP How to Set Up D2M for Your Project is not visible when the plot area is selected so you will have to select deselect to see the results of your adjustments 36 3 If you absolutely have to move or resize the map enter map1 in the Name box to select it and follow same directions as in step 36 2 See Appendix A Troubleshooting and Tips if you need to crop your maps 36 4 Continue doing a combination of all these steps as needed until sampling locations in samp data are aligned with sampling locations in map1 Yes this is a time consuming and tedious 36 5 Save your file Phew You re done with the hardest part Align other maps with Map1 If you moved or resized map1 you will have to do the same modifications to map2 and map3 so that they will still lie directly underneath 37 If you have a map2 37 1 Click on SHOW MAP and SHOW IMAGE to see how much and where map2 needs to be moved 37 2 Select map2 by entering map2 in the Name box 37 3 Resize move map2 until its edges line up with those of map 38 If you have a map3 38 1 Click on SHOW MAP and SHOW MUNICIP to see how much and where map3 needs t
66. ta Entry worksheet the parameters are lined up horizontally while the Param Info worksheet listed them vertically We will to change the orientation of the data when we paste 12 In the Data Entry worksheet select the first cell marked Flow It will be either cell E7 or F7 depending on which template you are using Right Click and select Paste Special Excel will fill that cell and the cells to the right with your copied cells A B Cc D E G H J K 1 DATA ENTRY SHEET 2 Once the sheet has been customized for your project you can enter data starting in row 8 3 Do not change the column headers after customization or queries will not work 4 5 6 acres storm base cfs is deg C mgt n Drainage tow TF Date Site ID Site Name Area Event y at pH Cc 69 6 28 2005 B Munson Ditch at US 12 840 base slow 8 70 628005 1B Waverly Road base stagnant wa 05 6 5 71 6 28 2005 2 West Tributary 3985 base slow D BS 2 6 28 2005 3 Dunes Creek pre culvert 7407 base fast 8 5 73 6 28 2005 4 Dunes Creek Outlet base medium 8 5 74 6 28 2005 5 Great Marsh Tributary base very slo 8 5 5 6 28 2005 6 East Tributary Hawleyvvood Road base stagnant wa 5 9 76 6 28 2005 6 East Tributary Hawleywood Road South Puddle base stagnant wa Delete 6 28 2005 7 East Tributary downstream of US20 487 base slight 5 5 5 78 628 2005 8 East Tributary upstream of US20 base stagnant wa Clear Contents 5 9 79
67. tabase for data management and then import into D2M for visual display and output Querying Data To query your data by parameter and year follow the instructions in the left blue section of any of the Maps or Chart worksheets E tiai Lee DA emple 70s ee a e UTU Aug E Elid SE os A y o gg All Sampling Data for Temp 1000 i i Stardm t t deg E ie pe rag ie ti ia T L pew Poe ii l pepi n h oompie preg ES ea a0 e bata p ET bt ae T f iy iid UPDATE YEAR LIST E 2 X y 5 y ET 7 f Tiap 1 Chessa eaa sampled peat is a u m E i 1 eT aut F is E qj bie E Tisoni ree of Boke repte lols eel cis 2 et ee a a 4 7 le eeun When you click the Refresh Charts Maps button in the final step the map and charts will be refreshed using the data query results Note Running a query from any of the worksheets will refresh the maps and charts in all the worksheets 3 Overview of Data2Maps Viewing Results After you have gone through the steps above all the D2M maps and chart worksheets will display your query results Here are the four components for each map layout SHOW MAP SHOW IMAGE SHOW MUNICIP All Sampling Data for Temp 2006 4 Standard 19 deg C fast updated July 27 2007 4 05 PM 16 All samples in compliance 14 1 sample not in compliance Site 12 1 sample not in compliance 12 No Data ei 93 Site 11 Site 15 Site g Site 1
68. the name param list so that it points to the parameters you previously entered in the Param Info worksheet in column A in Step 5 on pg 15 3 1 Click on the name param list from the Names in workbook dropdown list 3 2 Click on the button right side of the Refers to field Define Name Names in workbook param_list a param_lkup Param_page param_temp Print_Area MAP OPTION 1 rangeQ1 range02 rangeQ3 rangel4 range05 Mi Refers to Param Info i A 4 A 16 STEP BY STEP How to Set Up D2M for Your Project 3 3 Select the Param Info worksheet by clicking on the PARAM INFO tab and then select the parameters you entered in column A To enter a tine break click ALT ENTER Maps will show STANDARD standardthreshald entered units entered witht 1 lf PARAM is Dissolved P maps will show THRESHOLD standard thresh compliance STANDARD UNITS PARAM THRESHOLD label for y axes Cc Dissolved P 7 0 51 Dissolved Phosphorus Summary Analysis 4 Define Name Refers to t s 4 peer POP Seer rere ua smapling Concetrations from 0 5 2 mgA could be the thre graphs has been set at 0 5 mgA for a conservative revies Bacteria 230 mad Chloride Summary Analysis This is where Analysis Summary goes Conductivity j 1000 mg diss solids Conductivity Summary Analysis This is where analysis summary goes DO i 4 mgl DO Summary Analysis Reminder In this ca
69. u want to follow along in a Data2Maps file first go to Pg 5 for directions on how to avoid Excel security problems when you open the file D2M Worksheets Data2Maps is entirely contained in one Excel file with multiple worksheets Note To move through the worksheets click on the names on the bottom tabs There are 2 data entry related worksheets M ee naimar Cig n a ob BARA ONY MAP OPTION en OPTION 2 fae Corie 1 fet rea j pita h Eti cy a s A ae oe DATA ENTRY Worksheet This is where you enter your monitoring data All maps and charts use the data entered in this worksheet PARAM INFO Worksheet This worksheet contains a list of the sampling parameters and associated information e g units threshold and is populated during the set up process Users can also enter parameter specific comments here to be displayed appear with the maps There are 4 worksheets that map and or chart your monitoring data in different layouts Deciding which layout to choose depends on the end purpose as detailed below All Sampling Data for Nitrate nitrogen 2005 2006 Standard 1 mg ARM oped Ma TOO E Aa mi Sead oppkra bi pubis aed brad priti irg me puppie MAP OPTION 1 Worksheet Displays a map with sampling locations color coded by frequency of exceedances above a standard for a chosen time period Charts for each location are positioned on the side close to the sampling point on the map for easy associat
70. worksheets or the Charts worksheet active by clicking on the tab at the bottom of the window The query tool is available in the blue section on the left DPA boereep lal vF xh Poek Tools gwa Hehee rke pe Aiie BH a ad i OE Ajij pah UNIGP Dissolved P Armimania nitrogen Bacteria Nitrate Nitrite UPDATE YEAR LIST E te tS ft A a te OW DATA ENT MAP OPTION 1 MAP OPTION 2 J MAP OPTIC iy REFRESH CHARTS MAPS 14 In Step 1 you will see a list of the parameters that were set up for this project Click on a single parameter of interest 15 In Step 2 click the Update Year List button This will refresh the choice of sampling years in Step 3 16 If the list of sampling years in Step 3 is populated choose one year or All if you want to see data over all years If the list is empty there is no sampling data for that parameter and you will have to choose another parameter 17 In Step 4 click Refresh Charts Maps Note Only click this button if you have a parameter and year or All chosen Otherwise you will get a run time error STEP BY STEP How to Use Data2Maps 8 Viewing Maps Charts After you have gone through the steps above all the D2M maps and chart worksheets will display your query results Here are the four components for each map layout SHOW MAP SHOW IMAGE SHOW MUNICIP All Sampling Data for Temp 2006 4 Standard 19 deg C fast updated July 27 20
71. y obvious that E coli is a concern Dissolved o Fhosphorous data can help tine this information 43 Click on any of the labels You will see the label selected with hash marks You will also see hollow circle handles around the full extent of the labels indicating that the labels are grouped We will now ungroup these labels so that we can make edits fast updated June 27 2007 3 44 PM O 1 6 O S 14 IGUE fi A AA 11 9 13 10 0 S 8 15 TANI 33 7 6 5 4 O o S 51 STEP BY STEP How to Set Up D2M for Your Project 43 1 Right click on the hash mark border around the label you are changing epigenee n we an sh 43 2 Choose Grouping gt Ungroup If Ungroup is grayed out then choose Regroup and then do Grouping gt Ungroup 43 3 Deselect by clicking on a cell outside the map The 16 site labels on the worksheet are now ready for editing For up to 16 of your sampling locations you will rename of the current labels and move it next to the associated sampling location see the map with site names created by your GIS person per Appendix B Creating Maps for Data2Maps step 8 44 With the 16 labels textboxes currently on the worksheet 44 1 Choose a textbox and swipe over the text in it iC B 44 2 Type in one of your sampling location names Do not click off of it yet 44 3 Select the textbox by clicking on the hash marks surrounding the selected text MD Te Ke 4

Data2Maps (D2M) User Manual – July 2009

Contents

Download Pdf Manuals

Related Search

Related Contents

Data2Maps (D2M) User Manual &ndash; July 2009

Contents

Download Pdf Manuals

Related Search

Related Contents

Data2Maps (D2M) User Manual – July 2009