c_schleimer
New Member
- Joined
- Apr 30, 2015
- Messages
- 2
I have a list of pursuits for my company that I am trying make easily digestible by the brokers. The current list we have is about 500 lines long of data that looks like this:
<tbody>
</tbody>
Each line is its own pursuit with new ones being added and stuff changing within the pursuits weekly. So I am looking to be able to keep this big sheet and edit the data within it, and then in another sheet just have the brokers be able to type in/select their criteria and get a more concise view of the pursuits. They do not want to use the filter option (Don't ask me why, I know it's probably by far the easiest method).
I have the second sheet set up like this:
<tbody>
</tbody>
The bottom row above is the header for the table that the extracted data from the first sheet will go. The top three rows and columns C-H are where the brokers select the parameters by which to filter. Columns K thru O are conditionally formatted to show where in the pursuit process they are. For example: If they are in the pitch stage, Columns K, L, M, and N are shaded in. I know how to do that, so I'm just letting you get a feel for the layout.
Because there are multiple brokers on a single pursuit, I would like the "Brokers" column to be able to read "Chris/Joe/Jim" if those are the three Brokers on the pursuit (such as in pursuit #2 in the example), so that Joe can see which pursuits he is not only lead broker on, but second and third as well when he does his search.
What I'm thinking, is because each pursuit has its own unique identifier number (column A), doing some sort of index/match function/array formulain column A of the second sheet that pulls the pursuit numbers that meet the criteria at the top, then doing a vlookup for the rest of the data based on that identifier number. I tried doing the IFERROR/INDEX/SMALL/IF/ROW/ROWS method, but I couldn't figure out if I wanted to select more than one broker or more than one priority level from the parameters.
Again, I know that just slapping the filters on the columns on the first sheet probably makes the most sense, but I am looking for a way to accomplish this with formulas instead. My VBA skills/knowledge is pretty much nonexistent, so I would like to stick to formulas, but I am definitely open to learning how I could do this with VBA if that's the only method you can think of using.
If there is another thread or tutorial somewhere that shows me how to do what I am looking for, please point me in that direction. I thank you all in advance very much.
P.S. I have Excel 2013.
| # | Pursuit Status | Priority | DITM? | Broker #1 | Broker #2 | Broker #3 | Company | Size (Sqft) | Expiration Date | Building/Location | Previous Broker | Comments | Top Team Priority | ||
| 1 | Priority 2 - Medium | Chris | Jim | Bank | 7550 | 11/30/2015 | American Center | John | Jason has strong contact | Y | |||||
| 2 | Meeting | Priority 1 - High | Y | Chris | Joe | Jim | Law Firm | 19,795 | 2/1/2017 | The Park Bldg 1 | Pat |
<tbody> </tbody> | Y | ||
| 3 | Pitch | Priority 1 - High | Jim | Chris | Kristi | Pied Piper | 4,000 | 4/1/2018 | 123 E 5th St | Likely to interview for brokers in the Spring (2015) | Y |
<tbody>
</tbody>
Each line is its own pursuit with new ones being added and stuff changing within the pursuits weekly. So I am looking to be able to keep this big sheet and edit the data within it, and then in another sheet just have the brokers be able to type in/select their criteria and get a more concise view of the pursuits. They do not want to use the filter option (Don't ask me why, I know it's probably by far the easiest method).
I have the second sheet set up like this:
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P |
| Select Broker: | (Have a drop down check list of the brokers' names to select from, including "select all brokers". There are 16 brokers) | Top Team Priority? | (Y/N Option) | Other Filter 1: | (Some other parameter by which to filter) | ||||||||||
| Select Priority Level: | (Have a drop down check list of priority 1, 2, and 3, with the option to select multiple) | Minimum Size: | (Type in minimum sqft by which to filter) | Other Filter 2: | (Some other parameter by which to filter) | ||||||||||
| Select Status: | (Have a drop down to select where in the pursuit process the broker is: blank, Initial Contact, Meeting, Pitch, Recon/Research, Signed Contract) | Maximum Size: | (Type in maximum sqft by which to filter) | Other Filter 3: | (Some other parameter by which to filter) | ||||||||||
| Status ---------- | -------- | -------- | ----- | -------> | |||||||||||
| # | Brokers | Priority Level | Company | Our Client? | Size | Expiration Date | Building/Location | Previous Broker | Comments | Recon/Research | Initial Contact | Meeting | Pitch | Signed Contract | DITM? |
<tbody>
</tbody>
The bottom row above is the header for the table that the extracted data from the first sheet will go. The top three rows and columns C-H are where the brokers select the parameters by which to filter. Columns K thru O are conditionally formatted to show where in the pursuit process they are. For example: If they are in the pitch stage, Columns K, L, M, and N are shaded in. I know how to do that, so I'm just letting you get a feel for the layout.
Because there are multiple brokers on a single pursuit, I would like the "Brokers" column to be able to read "Chris/Joe/Jim" if those are the three Brokers on the pursuit (such as in pursuit #2 in the example), so that Joe can see which pursuits he is not only lead broker on, but second and third as well when he does his search.
What I'm thinking, is because each pursuit has its own unique identifier number (column A), doing some sort of index/match function/array formulain column A of the second sheet that pulls the pursuit numbers that meet the criteria at the top, then doing a vlookup for the rest of the data based on that identifier number. I tried doing the IFERROR/INDEX/SMALL/IF/ROW/ROWS method, but I couldn't figure out if I wanted to select more than one broker or more than one priority level from the parameters.
Again, I know that just slapping the filters on the columns on the first sheet probably makes the most sense, but I am looking for a way to accomplish this with formulas instead. My VBA skills/knowledge is pretty much nonexistent, so I would like to stick to formulas, but I am definitely open to learning how I could do this with VBA if that's the only method you can think of using.
If there is another thread or tutorial somewhere that shows me how to do what I am looking for, please point me in that direction. I thank you all in advance very much.
P.S. I have Excel 2013.
