Hi -
Thank you in advance for help on this!
I am trying to create a macro (or maybe multiple?) that looks at a a few fields in a "Raw Data" tab and exports results into a tab called "Opportunities". I have 4 categories listed in a column ("Lifestyle", "Multi", "Tech", "Auto" ). I am trying to create 4 separate buttons in the Opportunities tab for each that follow the following rules:
Data to output into Opportunities
<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>
- Things to keep in mind:
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
Thank you in advance for help on this!
I am trying to create a macro (or maybe multiple?) that looks at a a few fields in a "Raw Data" tab and exports results into a tab called "Opportunities". I have 4 categories listed in a column ("Lifestyle", "Multi", "Tech", "Auto" ). I am trying to create 4 separate buttons in the Opportunities tab for each that follow the following rules:
- If Sales POD (Column S) = "Lifestyle", "Multi", "Tech", "Auto"
- If Probability (%) (Column K) >=75
- Then output the results in the "Opportunities" tab (cant figure out how to post a screen shot but I'd want it to look like the below)
- Then autosize columns
- Then format columns C-E to be currency
Data to output into Opportunities
Opportunity Name | Opportunity Owner | New | Renewal | Carryover |
Opportunity 123 | Bob Smith | $109,223 | $40,774 | |
Opportunity 128 | Bob Smith | $31,060 |
<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>
- Things to keep in mind:
- I want the "Opportunities" tab to clear / refresh every time to allow for new data to be put in
- I don't want to output all of the columns in the Raw Data tab in the Opportunities tab. Only the few listed above
- I want to pull data based on if I pick "Lifestyle", "Multi", "Tech" or "Auto" (why I am thinking multiple buttons?)
- Column M (R Type) has "New", "Renewal" and "Carryover" relate to "CM Prod" (Column Q)
- So - in the Opportunities output the $ amount in column Q should be appropriately distributed based on the Opportunity Name (Column C)
- EX - There could be an Opportunity with the same name (see first two rows) but has $ amount in two different categories (in this example there is $ in Renewal and Carryover)
Opportunity Owner | Account Name | Opportunity Name | Client Category | 2018 Digital Region | 2018 Region | Venue | City | State | Days at Prob | Probability (%) | Product Name | R Type | S Inventory | S Prod | Expen S | CM PROD | Days Bucket | Sales POD | S or D | Account POD |
Bob Smith | Account 123 | Opportunity 123 | Food | Unknown | Region 1: East | Venue 123 | Atlanta | GA | 55 | 100 | Sponsorship Products | Renewal | XXX | $111,501 | $45,068 | $109,223 | 40+ | Multi | S | Multi |
Bob Smith | Account 123b | Opportunity 123 | Technology | Unknown | Region 1: East | Venue 124 | Atlanta | GA | 249 | 100 | Sponsorship Products | Carryover | XXX | $3,149 | $2,146 | $40,774 | 40+ | Enterprise | S | Tech |
Bob Smith | Account 125 | Opportunity 125 | Alcohol | Unknown | Region 1: East | Venue 125 | Atlanta | GA | 144 | 100 | Sponsorship Products | Renewal | XXX | $36,876 | $92,037 | $45,768 | 40+ | Lifestyle | S | Lifestyle |
Bob Smith | Account 126 | Opportunity 126 | Alcohol | Unknown | Region 1: East | Venue 126 | Atlanta | GA | 217 | 100 | Sponsorship Products | Carryover | XXX | $36,338 | $44,417 | $121,225 | 40+ | Lifestyle | S | Lifestyle |
Bob Smith | Account 127 | Opportunity 127 | Financial Services | Unknown | Region 1: East | Venue 127 | Atlanta | GA | 230 | 100 | Sponsorship Products | Carryover | XXX | $99,261 | $83,738 | $21,815 | 40+ | N/A | S | Multi |
Bob Smith | Account 128 | Opportunity 128 | Technology | Unknown | Region 1: East | Venue 128 | Atlanta | GA | 61 | 100 | Sponsorship Products | New | XXX | $112,986 | $5,710 | $31,060 | 40+ | Tech | S | Tech |
Bob Smith | Account 129 | Opportunity 129 | Energy Drink | Unknown | Region 1: East | Venue 129 | Atlanta | GA | 1 | 100 | Sponsorship Products | Renewal | XXX | $33,692 | $97,260 | $52,841 | 0 - 5 | Multi | S | Multi |
Bob Smith | Account 130 | Opportunity 130 | Travel | Unknown | Region 1: East | Venue 130 | Atlanta | GA | 230 | 100 | Sponsorship Products | Carryover | XXX | $103,634 | $29,028 | $58,450 | 40+ | Lifestyle | S | Lifestyle |
Bob Smith | Account 131 | Opportunity 131 | Alcohol | Unknown | Region 1: East | Venue 131 | Atlanta | GA | 85 | 85 | Sponsorship Products | Renewal | XXX | $114,605 | $81,118 | $88,640 | 40+ | N/A | S | Lifestyle |
Bob Smith | Account 132 | Opportunity 132 | Alcohol | Unknown | Region 1: East | Venue 132 | Atlanta | GA | 85 | 85 | Sponsorship Products | Renewal | XXX | $93,009 | $103,432 | $77,888 | 40+ | N/A | S | Lifestyle |
Bob Smith | Account 133 | Opportunity 133 | Alcohol | Unknown | Region 1: East | Venue 133 | Atlanta | GA | 85 | 85 | Sponsorship Products | Renewal | XXX | $101,425 | $95,077 | $56,486 | 40+ | N/A | S | Lifestyle |
Bob Smith | Account 134 | Opportunity 134 | Telecommunications | Unknown | Region 2: West | Venue 134 | New York | NY | 330 | 100 | Sponsorship Products | Carryover | XXX | $124,886 | $6,294 | $60,924 | 40+ | C&T | S | Tech |
Bob Smith | Account 135 | Opportunity 135 | Alcohol | Unknown | Region 2: West | Venue 135 | New York | NY | 144 | 100 | Sponsorship Products | Renewal | XXX | $107,249 | $120,698 | $26,576 | 40+ | Lifestyle | S | Lifestyle |
Bob Smith | Account 136 | Opportunity 136 | Energy Drink | Unknown | Region 2: West | Venue 136 | New York | NY | 230 | 100 | Sponsorship Products | Carryover | XXX | $43,519 | $111,415 | $855 | 40+ | N/A | S | Lifestyle |
Bob Smith | Account 137 | Opportunity 137 | Financial Services | Unknown | Region 2: West | Venue 137 | New York | NY | 230 | 100 | Sponsorship Products | Carryover | XXX | $21,369 | $73,453 | $64,164 | 40+ | N/A | S | Multi |
Bob Smith | Account 138 | Opportunity 138 | Energy Drink | Unknown | Region 2: West | Venue 138 | New York | NY | 1 | 100 | Sponsorship Products | Renewal | XXX | $7,859 | $83,157 | $57,123 | 0 - 5 | Multi | S | Multi |
Bob Smith | Account 139 | Opportunity 139 | Travel | Unknown | Region 2: West | Venue 139 | New York | NY | 230 | 100 | Sponsorship Products | Carryover | XXX | $17,357 | $101,002 | $82,221 | 40+ | Lifestyle | S | Lifestyle |
Bob Smith | Account 140 | Opportunity 140 | Food | Unknown | Region 1: East | Venue 140 | Tampa | FL | 55 | 100 | Digital Products | Renewal | XXX | $12,492 | $96,449 | $26,979 | 40+ | Multi | S | Multi |
Bob Smith | Account 141 | Opportunity 141 | Technology | Unknown | Region 1: East | Venue 141 | Tampa | FL | 249 | 100 | Digital Products | Carryover | XXX | $10,112 | $76,557 | $92,298 | 40+ | Enterprise | S | Tech |
Bob Smith | Account 142 | Opportunity 142 | Alcohol | Unknown | Region 1: East | Venue 142 | Tampa | FL | 144 | 100 | Digital Products | Renewal | XXX | $62,251 | $111,068 | $24,511 | 40+ | Lifestyle | S | Lifestyle |
Bob Smith | Account 143 | Opportunity 143 | Energy Drink | Unknown | Region 1: East | Venue 143 | Tampa | FL | 230 | 100 | Digital Products | Carryover | XXX | $54,520 | $102,905 | $31,973 | 40+ | N/A | S | Lifestyle |
Bob Smith | Account 144 | Opportunity 144 | Financial Services | Unknown | Region 1: East | Venue 144 | Tampa | FL | 230 | 100 | Digital Products | Carryover | XXX | $86,803 | $8,869 | $118,660 | 40+ | N/A | S | Multi |
Bob Smith | Account 145 | Opportunity 145 | Technology | Unknown | Region 1: East | Venue 145 | Tampa | FL | 61 | 100 | Digital Products | New | XXX | $118,992 | $4,577 | $53,738 | 40+ | Tech | S | Tech |
Bob Smith | Account 146 | Opportunity 146 | Energy Drink | Unknown | Region 1: East | Venue 146 | Tampa | FL | 1 | 100 | Digital Products | Renewal | XXX | $58,141 | $75,398 | $70,245 | 0 - 5 | Multi | S | Multi |
Bob Smith | Account 147 | Opportunity 147 | Automotive | Unknown | Region 1: East | Venue 147 | Tampa | FL | 81 | 100 | Digital Products | Renewal | XXX | $18,589 | $66,556 | $69,673 | 40+ | Multi | S | Auto |
Bob Smith | Account 148 | Opportunity 148 | Travel | Unknown | Region 1: East | Venue 148 | Tampa | FL | 230 | 100 | Digital Products | Carryover | XXX | $119,560 | $27,868 | $8,312 | 40+ | Lifestyle | S | Lifestyle |
Bob Smith | Account 149 | Opportunity 149 | Alcohol | Unknown | Region 1: East | Venue 149 | Tampa | FL | 85 | 85 | Digital Products | Renewal | XXX | $50,713 | $78,221 | $11,109 | 40+ | N/A | S | Lifestyle |
Bob Smith | Account 150 | Opportunity 150 | Alcohol | Unknown | Region 1: East | Venue 150 | Tampa | FL | 85 | 85 | Digital Products | Renewal | XXX | $89,270 | $33,972 | $108,332 | 40+ | N/A | S | Lifestyle |
Bob Smith | Account 151 | Opportunity 151 | Alcohol | Unknown | Region 1: East | Venue 151 | Tampa | FL | 85 | 85 | Digital Products | Renewal | XXX | $51,667 | $55,737 | $70,271 | 40+ | N/A | S | Lifestyle |
Bob Smith | Account 152 | Opportunity 152 | Alcohol | Unknown | Region 2: West | Venue 152 | San Francisco | CA | 144 | 100 | Digital Products | Renewal | XXX | $26,202 | $79,538 | $120,738 | 40+ | Lifestyle | S | Lifestyle |
Bob Smith | Account 153 | Opportunity 153 | Energy Drink | Unknown | Region 2: West | Venue 153 | San Francisco | CA | 230 | 100 | Digital Products | Carryover | XXX | $99,693 | $56,440 | $40,579 | 40+ | N/A | S | Lifestyle |
Bob Smith | Account 154 | Opportunity 154 | Financial Services | Unknown | Region 2: West | Venue 154 | San Francisco | CA | 230 | 100 | Digital Products | Carryover | XXX | $17,758 | $37,946 | $43,107 | 40+ | N/A | S | Multi |
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>