Hi,
(Table Inserted with HTML writer below text)
I am working on a automated report that generates a list of project names (column B) and their associated dollar values (Column C) using the following criteria:
1. Month, (I have created a drop down menu with a "=month" function that converts the selected month into its associated number)
2. Fiscal Year, (Our fiscal year starts sept. 1st; again another drop down menu)
3. Building Unit Type, (MBU, IBU, DBU; different divisions within the company and again another drop down menu)
4. Projects over $100million,
5. Projects under 100 million but over 50m,
6. Projects under 50m but over 25m,
7. Projects under 25m,
8. and of course, whether our bid was successful or not (columm F = rank must equal 1)
I have already created a function that can sort the dollar values of projects we have won the bid on (based on the criteria above); this allows us to project construction volumes. Also, more functions that determine our success rate etc.
I have come up with a formula combining Array and Match but that simply returns the same job name (text string) over and over again. This is because there is multiple job names in the same month, but they have the same criteria; the Array-Match function will only show the first result, not list them.
This form is used by an executive and therefore must be automated. The file itself is updated in Microsoft access by a receptionist; then exported and overwrites the file called table1.xls (possibly xlsx, I just had my computer updated to 2010 from 2003 lol). I have spent a bit of time trying to figure it out, and cannot seem to do it. So hey, any help at all would be appreciated. Something tells me excel functions were not designed for this and a VBA solution may be required. No need to come up with a turn-key solution, and new ideas would be awesome.
Thank you for your time,
Mark
***Note: all of this data is fake, as I cannot divulge private information.
Excel 2010
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
(Table Inserted with HTML writer below text)
I am working on a automated report that generates a list of project names (column B) and their associated dollar values (Column C) using the following criteria:
1. Month, (I have created a drop down menu with a "=month" function that converts the selected month into its associated number)
2. Fiscal Year, (Our fiscal year starts sept. 1st; again another drop down menu)
3. Building Unit Type, (MBU, IBU, DBU; different divisions within the company and again another drop down menu)
4. Projects over $100million,
5. Projects under 100 million but over 50m,
6. Projects under 50m but over 25m,
7. Projects under 25m,
8. and of course, whether our bid was successful or not (columm F = rank must equal 1)
I have already created a function that can sort the dollar values of projects we have won the bid on (based on the criteria above); this allows us to project construction volumes. Also, more functions that determine our success rate etc.
I have come up with a formula combining Array and Match but that simply returns the same job name (text string) over and over again. This is because there is multiple job names in the same month, but they have the same criteria; the Array-Match function will only show the first result, not list them.
This form is used by an executive and therefore must be automated. The file itself is updated in Microsoft access by a receptionist; then exported and overwrites the file called table1.xls (possibly xlsx, I just had my computer updated to 2010 from 2003 lol). I have spent a bit of time trying to figure it out, and cannot seem to do it. So hey, any help at all would be appreciated. Something tells me excel functions were not designed for this and a VBA solution may be required. No need to come up with a turn-key solution, and new ideas would be awesome.
Thank you for your time,
Mark
***Note: all of this data is fake, as I cannot divulge private information.
Excel 2010
| A | B | C | D | E | F | G | H | I | J | K | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Tender No | PROJECT | TENDER AMOUNT | GENERAL CONTRACTOR | TENDER CLOSING | OVERALL RANK | TOTAL BIDDERS | MARK UP | RISK ALLOWANCE | BUILDING UNIT | FISCAL YEAR |
| 2 | PORT GRANBY WTP | $400,000.00 | GC1 | 18-Dec-12 | 1 | 4 | 0.00% | $0.00 | MBU | 2013 | |
| 3 | PORT GRANBY WTP | $410,000.00 | GC2 | 18-Dec-12 | 2 | 4 | 0.00% | $0.00 | MBU | 2013 | |
| 4 | PORT GRANBY WTP | $420,000.00 | GC3 | 18-Dec-12 | 3 | 4 | 0.00% | $0.00 | MBU | 2013 | |
| 5 | PORT GRANBY WTP | $430,000.00 | GC4 | 18-Dec-12 | 4 | 4 | 0.00% | $0.00 | MBU | 2013 | |
| 6 | BARNSTON PS | $400,000.00 | GC1 | 17-Jan-13 | 1 | 4 | 0.00% | $0.00 | MBU | 2013 | |
| 7 | BARNSTON PS | $410,000.00 | GC2 | 17-Jan-13 | 2 | 4 | 0.00% | $0.00 | MBU | 2013 | |
| 8 | BARNSTON PS | $420,000.00 | GC3 | 17-Jan-13 | 3 | 4 | 0.00% | $0.00 | MBU | 2013 | |
| 9 | BARNSTON PS | $430,000.00 | GC4 | 17-Jan-13 | 4 | 4 | 0.00% | $0.00 | MBU | 2013 | |
| 10 | MEDICINE HAT WTP UPGRADES | $400,000.00 | GC1 | 16-Feb-13 | 1 | 4 | 0.00% | $0.00 | MBU | 2013 | |
| 11 | MEDICINE HAT WTP UPGRADES | $410,000.00 | GC2 | 16-Feb-13 | 2 | 4 | 0.00% | $0.00 | MBU | 2013 | |
| 12 | MEDICINE HAT WTP UPGRADES | $420,000.00 | GC3 | 16-Feb-13 | 3 | 4 | 0.00% | $0.00 | MBU | 2013 | |
| 13 | MEDICINE HAT WTP UPGRADES | $430,000.00 | GC4 | 16-Feb-13 | 4 | 4 | 0.00% | $0.00 | MBU | 2013 | |
| 14 | NANAIMO RESERVOIR NO. 1 | $400,000.00 | GC1 | 18-Mar-13 | 1 | 4 | 0.00% | $0.00 | MBU | 2013 | |
| 15 | NANAIMO RESERVOIR NO. 1 | $410,000.00 | GC2 | 18-Mar-13 | 2 | 4 | 0.00% | $0.00 | MBU | 2013 | |
| 16 | NANAIMO RESERVOIR NO. 1 | $420,000.00 | GC3 | 18-Mar-13 | 3 | 4 | 0.00% | $0.00 | MBU | 2013 | |
| 17 | NANAIMO RESERVOIR NO. 1 | $430,000.00 | GC4 | 18-Mar-13 | 4 | 4 | 0.00% | $0.00 | MBU | 2013 | |
| 18 | DUFFIN CREEK WPCP STAGE 3 INFLUENT PS | $400,000.00 | GC1 | 17-Apr-13 | 1 | 4 | 0.00% | $0.00 | MBU | 2013 | |
| 19 | DUFFIN CREEK WPCP STAGE 3 INFLUENT PS | $410,000.00 | GC2 | 17-Apr-13 | 2 | 4 | 0.00% | $0.00 | MBU | 2013 | |
| 20 | DUFFIN CREEK WPCP STAGE 3 INFLUENT PS | $420,000.00 | GC3 | 17-Apr-13 | 3 | 4 | 0.00% | $0.00 | MBU | 2013 | |
| 21 | DUFFIN CREEK WPCP STAGE 3 INFLUENT PS | $430,000.00 | GC4 | 17-Apr-13 | 4 | 4 | 0.00% | $0.00 | MBU | 2013 |
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1
