zookeepertx
Well-known Member
- Joined
- May 27, 2011
- Messages
- 568
- Office Version
- 365
- Platform
- Windows
I have a workbook with a number of sheets in it, 1 for each month (shows new vendors added that month), plus a summary sheet (called Tracking), & a couple of others. The monthly sheets are formatted like this:
<TBODY>
</TBODY>
(The above data is in columns F, H, J, N, P, R, T, V & AF. I've got the rest of the columns hidden, since that data is not used for this.)
I need to report, preferably on the Tracking sheet, the total number of new vendors by Division & the total number of new departments by Division. (STORES and LAST CALL = Div XYZ, AB = Div AB, Direct = Div MNO)
I started with some code that I found by using a search on this board & have got it to where it will
<TBODY>
</TBODY>
That's okay, but it'd be better if I could get the counts to appear in the appropriate cell on the Tracking worksheet.
The monthly tabs are called NV JANUARY, NV FEBRUARY, etc. This is what the Tracking sheet looks like:
<TBODY>
</TBODY>
Can anyone tell me how I could get the counts to appear in their proper cell on Tracking instead of below the data on the monthly sheet? (It's not my spreadsheet, so I can't change the names of the tabs).
Thank you for any help!!
| Division | Vendor Name | Dept | EDI CAPABLE | Vendor Ticket | PreTicket | Packing List | Carton Label | TT |
| Left Message For Vendor | No Response From Vendor | Awtg More Information | ||||||
| STORES | NOTIFY ME | 499 | NO | YES | YES | |||
| AB | JAMES WOLF | 23 | NO | YES | NO | NO | NO | |
| AB | VIVIENNE WEST | 27 | ||||||
| STORES | ROCKFIELD | 269 | NO | NO | YES | |||
| STORES | RATTLER | 499 | YES | NO | NO | YES | ||
| DIRECT | COUNTRY ROADS | 522 | ||||||
| DIRECT | INNOVATIONS | 970 | YES | NO | NO | NO | YES | |
| DIRECT | INNOVATIONS | 971 | YES | NO | NO | NO | YES | |
| DIRECT | COUNTRY ROADS | 523 | ||||||
| DIRECT | COUNTRY ROADS | 531 | ||||||
| DIRECT | INNOVATIONS | 599 | YES | NO | NO | NO | ||
| LAST CALL | HOUSTON PRODUCTS | 773 | ||||||
| DIRECT | COUNTRY ROADS | 972 | ||||||
| DIRECT | ILLUSTRIOUS | 990 | NO | YES | NO | |||
| STORES | JULES VERNE | 883 | NO | NO | NO | |||
| STORES | ELLA PUTNAM | 886 | YES | NO | NO | NO | NO | |
| AB | JOHN DOE COSMETICS | 21 | YES | NO | NO | YES | YES | |
| DIRECT | CHARLIE JONES | 887 | ||||||
| DIRECT | 4 COLLECTIONS | 888 | YES | NO | NO | NO | NO | |
| DIRECT | ALEX SMITH | 938 | YES | NO | NO | YES | NO | |
| DIRECT | DEREK ROGERS | 959 | ||||||
| DIRECT | INNOVATIONS | 968 | YES | NO | NO | NO | ||
| DIRECT | INNOVATIONS | 969 | YES | NO | NO | NO |
<TBODY>
</TBODY>
(The above data is in columns F, H, J, N, P, R, T, V & AF. I've got the rest of the columns hidden, since that data is not used for this.)
I need to report, preferably on the Tracking sheet, the total number of new vendors by Division & the total number of new departments by Division. (STORES and LAST CALL = Div XYZ, AB = Div AB, Direct = Div MNO)
I started with some code that I found by using a search on this board & have got it to where it will
- list the new vendors, by division, in columns AG - AI & provide a count of them below the data in the Vendor column.
- list the new depts, by division, in columns AJ - AL & provide a count of them below the data in the Dept column.
| Division | Vendor Name | Dept | EDI CAPABLE | Vendor Ticket | PreTicket | Packing List | Carton Label | TT | XYZ-NV | AB-NV | MNO-NM | XYZ-Dept | AB-Dept | MNO-Dept |
| Left Message For Vendor | No Response From Vendor | Awtg More Information | ELLA PUTNAM | JAMES WOLF | 4 COLLECTIONS | 269 | 21 | 522 | ||||||
| AB | JOHN DOE COSMETICS | 21 | YES | NO | NO | YES | YES | HOUSTON PRODUCTS | JOHN DOE COSMETICS | ALEX SMITH | 499 | 23 | 523 | |
| AB | JAMES WOLF | 23 | NO | YES | NO | NO | NO | JULES VERNE | VIVIENNE WEST | CHARLIE JONES | 773 | 27 | 531 | |
| AB | VIVIENNE WEST | 27 | NOTIFY ME | COUNTRY ROADS | 883 | 599 | ||||||||
| STORES | ROCKFIELD | 269 | NO | NO | YES | RATTLER | DEREK ROGERS | 886 | 887 | |||||
| STORES | NOTIFY ME | 499 | NO | YES | YES | ROCKFIELD | ILLUSTRIOUS | 888 | ||||||
| STORES | RATTLER | 499 | YES | NO | NO | YES | INNOVATIONS | 938 | ||||||
| DIRECT | COUNTRY ROADS | 522 | 959 | |||||||||||
| DIRECT | COUNTRY ROADS | 523 | 968 | |||||||||||
| DIRECT | COUNTRY ROADS | 531 | 969 | |||||||||||
| DIRECT | INNOVATIONS | 599 | YES | NO | NO | NO | 970 | |||||||
| LAST CALL | HOUSTON PRODUCTS | 773 | 971 | |||||||||||
| STORES | JULES VERNE | 883 | NO | NO | NO | 972 | ||||||||
| STORES | ELLA PUTNAM | 886 | YES | NO | NO | NO | NO | 990 | ||||||
| DIRECT | CHARLIE JONES | 887 | ||||||||||||
| DIRECT | 4 COLLECTIONS | 888 | YES | NO | NO | NO | NO | |||||||
| DIRECT | ALEX SMITH | 938 | YES | NO | NO | YES | NO | |||||||
| DIRECT | DEREK ROGERS | 959 | ||||||||||||
| DIRECT | INNOVATIONS | 968 | YES | NO | NO | NO | ||||||||
| DIRECT | INNOVATIONS | 969 | YES | NO | NO | NO | ||||||||
| DIRECT | INNOVATIONS | 970 | YES | NO | NO | NO | YES | |||||||
| DIRECT | INNOVATIONS | 971 | YES | NO | NO | NO | YES | |||||||
| DIRECT | COUNTRY ROADS | 972 | ||||||||||||
| DIRECT | ILLUSTRIOUS | 990 | NO | YES | NO | |||||||||
| 6 | 5 | |||||||||||||
| 3 | 3 | |||||||||||||
| 7 | 14 |
<TBODY>
</TBODY>
That's okay, but it'd be better if I could get the counts to appear in the appropriate cell on the Tracking worksheet.
The monthly tabs are called NV JANUARY, NV FEBRUARY, etc. This is what the Tracking sheet looks like:
| January | 2013 | ||||||||||||||
| Turn Time 2 | |||||||||||||||
| XYZ | AB | MNO | Total | ||||||||||||
| MTD | STD | YTD | MTD | STD | YTD | MTD | STD | YTD | MTD | STD | YTD | ||||
| New Vendor | 6 | 181 | 181 | 3 | 40 | 40 | 7 | 58 | 58 | 16 | 279 | 279 | |||
| Department Adds | 5 | 245 | 245 | 3 | 52 | 52 | 14 | 106 | 106 | 22 | 403 | 403 | |||
| OPERATIONAL CAPABILITIES | |||||||||||||||
| XYZ | AB | MNO | Total | ||||||||||||
| MTD | STD | YTD | MTD | STD | YTD | MTD | STD | YTD | MTD | STD | YTD | ||||
| Vendor Ticket | 22 | 22 | 2 | 2 | 8 | 8 | 0 | 32 | 32 | ||||||
| Pre-ticket | 13 | 13 | 6 | 6 | 0 | 0 | 0 | 19 | 19 | ||||||
| EDI | 11 | 11 | 1 | 1 | 4 | 4 | 0 | 16 | 16 | ||||||
| Packing/Ctn label | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||||||
| February | 2013 | ||||||||||||||
| Turn Time 2 | |||||||||||||||
| XYZ | AB | MNO | Total | ||||||||||||
| MTD | STD | YTD | MTD | STD | YTD | MTD | STD | YTD | MTD | STD | YTD | ||||
| New Vendor | 0 | 181 | 0 | 40 | 0 | 58 | 0 | 279 | |||||||
| Department Adds | 0 | 245 | 0 | 52 | 0 | 106 | 0 | 403 | |||||||
| OPERATIONAL CAPABILITIES | |||||||||||||||
| XYZ | AB | MNO | Total | ||||||||||||
| MTD | STD | YTD | MTD | STD | YTD | MTD | STD | YTD | MTD | STD | YTD | ||||
| Vendor Ticket | 0 | 22 | 0 | 2 | 0 | 8 | 0 | 32 | |||||||
| Pre-ticket | 0 | 13 | 0 | 6 | 0 | 0 | 0 | 19 | |||||||
| EDI | 0 | 11 | 0 | 1 | 0 | 4 | 0 | 16 | |||||||
| Packing/Ctn label | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
<TBODY>
</TBODY>
Can anyone tell me how I could get the counts to appear in their proper cell on Tracking instead of below the data on the monthly sheet? (It's not my spreadsheet, so I can't change the names of the tabs).
Thank you for any help!!
