I have a summary sheet that I need to display a sum on, for each employees' mileage for the month. There are worksheets in the workbook for each day in each month. Each worksheet is titled mm-dd-yy.
The summary data layout that we need is:
<tbody>
</tbody>
The employee Names are in alphabetical order on the summary sheet in column B. The list of employee names is populated by cell references to a worksheet named "Data" where all employee names are dynamically listed in A2:A101 and alphabetically sorted at Workbook_Open. In C3:C102 of the summary sheet, we need to add in the value of E3:E27 on the daily worksheets (represented by the table below) that match the employee names in the daily worksheets, in A3:A27 which may not be alphabetized like the Summary worksheet.
<tbody>
</tbody>
On a Table of contents page, there are hyperlinks to each daily worksheet and the text displayed is equal to the names of each worksheet. On the TOC worksheet, each month is listed in A1:L1. Each day in each month is listed down the appropriate column. I've tried to select the cells in each month and rename them as a range (i.e. "__January", "__February", "__March", etc, etc) to use in a formula for the summary worksheet. I couldn't get this to work with :
I thought that maybe the text representing the worksheet names, as hyperlinks was the problem...so I made a list of the sheet names on the table of contents page as plain text, in Q2:Q32 (for January). I then selected that range and named it "_January". I also couldn't get this to work in a formula.
So basically, I need help displaying if an employee's name appears on any daily worksheets of a month, tell me the total $ owed to them for that month's mileage, for each month.
The summary data layout that we need is:
| B | C | F | I |
| Employee Name | January | February | March |
| AAA, Employee | |||
| BBB, Employee | |||
| CCC, Employee |
<tbody>
</tbody>
The employee Names are in alphabetical order on the summary sheet in column B. The list of employee names is populated by cell references to a worksheet named "Data" where all employee names are dynamically listed in A2:A101 and alphabetically sorted at Workbook_Open. In C3:C102 of the summary sheet, we need to add in the value of E3:E27 on the daily worksheets (represented by the table below) that match the employee names in the daily worksheets, in A3:A27 which may not be alphabetized like the Summary worksheet.
| A | B | C | D | E |
| Employee Name | Date | Mileage | Rate | Total |
| DDD, Employee | 01-01-18 | 50 | 0.54 | $27.00 |
| NNN, Employee | 01-01-18 | 100 | 0.54 | $54.00 |
| BBB, Employee | 01-01-18 | 100 | 0.54 | $54.00 |
| AAA, Employee | 01-01-18 | 50 | 0.54 | $27.00 |
| ZZZ, Employee | 01-01-18 | 10 | 0.54 | $5.40 |
<tbody>
</tbody>
On a Table of contents page, there are hyperlinks to each daily worksheet and the text displayed is equal to the names of each worksheet. On the TOC worksheet, each month is listed in A1:L1. Each day in each month is listed down the appropriate column. I've tried to select the cells in each month and rename them as a range (i.e. "__January", "__February", "__March", etc, etc) to use in a formula for the summary worksheet. I couldn't get this to work with :
Code:
[COLOR=#000000][FONT='inherit'][SIZE=3]{=VLOOKUP([COLOR=#0057d6]B3[/COLOR],INDIRECT[COLOR=#006107]([/COLOR]"'"&INDEX[COLOR=#ab30d6]([/COLOR]_January,MATCH[COLOR=#a54a29]([/COLOR]1,--[COLOR=#33af4a]([/COLOR]SUMIF[COLOR=#ff9c1b]([/COLOR]INDIRECT[COLOR=#fe4fdd]([/COLOR]"'"&_January&"'!$A$3:$E$101"[COLOR=#fe4fdd])[/COLOR],[COLOR=#0057d6]B3[/COLOR][COLOR=#ff9c1b])[/COLOR]>0[COLOR=#33af4a])[/COLOR],0[COLOR=#a54a29])[/COLOR][COLOR=#ab30d6])[/COLOR]&"'!$A$3:$E$101"[COLOR=#006107])[/COLOR],5,FALSE)}[/SIZE][/FONT][/COLOR]I thought that maybe the text representing the worksheet names, as hyperlinks was the problem...so I made a list of the sheet names on the table of contents page as plain text, in Q2:Q32 (for January). I then selected that range and named it "_January". I also couldn't get this to work in a formula.
Code:
[COLOR=#000000][FONT='inherit'][SIZE=3]=SUMPRODUCT(SUMIF[COLOR=#006107]([/COLOR]INDIRECT[COLOR=#ab30d6]([/COLOR]"'"&_January&"'!"&"A3:A27"[COLOR=#ab30d6])[/COLOR],[COLOR=#0057d6]B3[/COLOR],INDIRECT[COLOR=#ab30d6]([/COLOR]"'"&_January&"'!"&"E3:E27"[COLOR=#ab30d6])[/COLOR][COLOR=#006107])[/COLOR])[/SIZE][/FONT][/COLOR]So basically, I need help displaying if an employee's name appears on any daily worksheets of a month, tell me the total $ owed to them for that month's mileage, for each month.
Last edited:
