Auto-Populate Cells Based on Input

JRC_Design

New Member
Joined
Jul 10, 2018
Messages
4
Hello Everyone! I'm a landscape architect and part of my job is working on Tree Preservation Plans. Basically what I'd like to do is input the Species, DBH, & ACTION (In Yellow) and have it populate the cell (In Green) based on the category in F3:N3 (Significant Trees, Heritage Trees, Etc..). The categories table has been attached as well.

I'm usually able to solve my Excel issues but this one is a bit over my head. I'd appreciate any solutions or suggestions you may have. Thanks in advance!!


ABCDEFGHIJKLMNOPQ
2DESCRIPTIONSMALL TREE / UNDERSTORY SPECIESTREESTREES
2.5" TO 6"
3TREE NUMBERSPECIESDBHACTIONCOMMENTSSIGNIFICANT TREES: 5" TO 12"HERITAGE TREES: GREATER THAN 12"SIGNIFICANT TREES: 6" TO 24"SIGNIFICANT TREES: 10" TO 24"HERITAGE TREES: GREATER THAN 24"+
4TO
REMAIN
TO BE
REMOVED
TO REMAINTO BE REMOVEDTO REMAINTO BE REMOVEDTO REMAINTO BE REMOVEDTO REMAINTO BE REMOVED (3:1 MITIGATION)TO BE REMOVED (1:1 MITIGATION)
51061LIVE OAK12PRESERVE12
61062HACKBERRY15REMOVE15
71063TEXAS PERSIMMON5PRESERVE5
81064PECAN40PRESERVE40
91065HACKBERRY28REMOVE28
101066TEXAS REDBUD8REMOVE8
111067BUR OAK6REMOVE6
121068MESQUITE17PRESERVE17
131069LIVE OAK24REMOVE24
141070TEXAS MOUNTAIN LAUREL12.5PRESERVE12.5
151071TEXAS PERSIMMON13REMOVE13
161072MONTERREY OAK4.5PRESERVE4.5
171073ANACACHO ORCHID TREE3REMOVEEXEMPT
181074CHINABERRY20REMOVEINVASIVE - EXEMPT
191075SALT CEDAR26.5REMOVEINVASIVE-EXEMPT
201076MESQUITE2PRESERVEEXEMPT
21
22SUBTOTAL INCHES5812.51312617154024284.5
23TOTAL INCHES BY CATEGORY1325.5183292
24
25UNDERSTORY PRESERVATION
26UNDERSTORY HERITAGE PRESERVATION
27
28SIGNIFICANT PRESERVATION
29HERITAGE PRESERVATION (1:1 MITIGATION)
30HERITAGE PRESERVATION (3:1 MITIGATION)
31
32MITIGATION REQUIRED
33HERITAGE MITIGATION REQUIRED
34
35No category to fall below 20% preservation;
36Preserved- Tree to remain that meets root protection zone requirements described in section 35-523 of the UDC.
37Mitigation 1:1 for significant trees below minimum preservation requirements; 3:1 for heritage trees below 100% preservation
38* Small species: Condalia, Redbud, Tx. Mountain Laurel, Tx. Persimmon, Hawthorn, Possumhaw - these are mitigated at 1:1 for Heritage Trees
39** Ashe Juniper, Huisache, Mesquite, Arizona Ash, Hackberry protected at 10" dbh and mitigated at 1:1 for heritage trees
40*** Mitigation Trees: Unprotected-sized trees to be used for mitigation calculations; subtract inches from mitigation owed
41**** Unprotected Trees: Removed not counted

<tbody>
</tbody>
TP - EXAMPLE
ABCDEFGHIJKLMNO
1SIGNIFICANT TREES: 5" TO 12"
HERITAGE TREES: GREATER THAN 12"SIGNIFICANT TREES: 10" TO 24"HERITAGE TREES: GREATER THAN 24"+ /// 1:1 MITIGATIONSIGNIFICANT TREES: 6" TO 24"HERITAGE TREES: GREATER THAN 24" /// 3:1 MITIGATIONTREES: 2.5" TO 6"NON-NATIVE /// INVASIVE
2TEXAS PERSIMMONTEXAS PERSIMMONASHE JUNIPERASHE JUNIPERAnacacho, Orchid tree*Anacacho, Orchid tree*TEXAS PERSIMMONCHINESE PISTACHE
3TEXAS REDBUDTEXAS REDBUDHUISACHEHUISACHEAnaqua*, Sandpaper treeAnaqua*, Sandpaper treeTEXAS REDBUDCHINABERRY
4TEXAS MOUNTAIN LAURELTEXAS MOUNTAIN LAURELMESQUITEMESQUITEArizona CypressArizona CypressTEXAS MOUNTAIN LAURELCHINESE TALLOW
5CONDALIACONDALIAARIZONA ASHARIZONA ASHBald Cypress**Bald Cypress**CONDALIATREE OF HEAVEN
6POSSUMHAWPOSSUMHAWHACKBERRYHACKBERRYBig Tooth Maple**Big Tooth Maple**POSSUMHAWSALT CEDAR
7HAWTHORNEHAWTHORNEFRAXINUSFRAXINUSBlack Willow*Black Willow*HAWTHORNEJAPANESE LIGUSTRUM
8Bur Oak*Bur Oak*ASHE JUNIPERNANDINA
9Carolina Buckthorn*Carolina Buckthorn*HUISACHEPAPER MULBERRY
10Cedar Elm*Cedar Elm*MESQUITE
11Chinquapin Oak*Chinquapin Oak*ARIZONA ASH
12Cottonwood*Cottonwood*HACKBERRY
13Crabapple, Texas*Crabapple, Texas*FRAXINUS
14Desert Willow*Desert Willow*Anacacho, Orchid tree*
15Deodar CedarDeodar CedarAnaqua*, Sandpaper tree
16Ebony, Texas*Ebony, Texas*Arizona Cypress
17Escarpment Black Cherry*Escarpment Black Cherry*Bald Cypress**
18Eve's Necklace*Eve's Necklace*Big Tooth Maple**
19Goldenball Lead Tree*Goldenball Lead Tree*Black Willow*
20Honey LocustHoney LocustBur Oak*
21Italian Stone pineItalian Stone pineCarolina Buckthorn*
22Kidneywood*Kidneywood*Cedar Elm*
23Lacy Oak*Lacy Oak*Chinquapin Oak*
24Live Oak*Live Oak*Cottonwood*
25Monterrey OakMonterrey OakCrabapple, Texas*
26Montezuma cypress**Montezuma cypress**Desert Willow*
27Mexican Buckeye*Mexican Buckeye*Deodar Cedar
28Pecan*Pecan*Ebony, Texas*
29Plum, Mexican*Plum, Mexican*Escarpment Black Cherry*
30Retama, Paloverde*Retama, Paloverde*Eve's Necklace*
31Red Oak, Shumard*Red Oak, Shumard*Goldenball Lead Tree*
32Red Oak, Texas*Red Oak, Texas*Honey Locust
33Rusty Blackhaw*Rusty Blackhaw*Italian Stone pine
34Silk-tassle*Silk-tassle*Kidneywood*
35Sycamore, MexicanSycamore, MexicanLacy Oak*
36Sycamore,Texas*Sycamore,Texas*Live Oak*
37Texas Pistache*Texas Pistache*Monterrey Oak
38Western Soapberry*Western Soapberry*Montezuma cypress**
39Wild Olive*Wild Olive*Mexican Buckeye*
40Yaupon Holly*Yaupon Holly*Pecan*
41Plum, Mexican*
42Retama, Paloverde*
43Red Oak, Shumard*
44Red Oak, Texas*
45Rusty Blackhaw*
46Silk-tassle*
47Sycamore, Mexican
48Sycamore,Texas*
49Texas Pistache*
50Western Soapberry*
51Wild Olive*
52Yaupon Holly*

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
CATEGORIES
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Watch MrExcel Video

Forum statistics

Threads
1,108,579
Messages
5,523,700
Members
409,532
Latest member
Lmfacc

This Week's Hot Topics

Top