I trying to develop a formula to return a variance amount from using a vlookup, hlookup, match or index.
The is the tab in which the formula would be entered.
<colgroup><col><col><col><col><col><col><col><col><col span="6"></colgroup><tbody>
</tbody>
The first problem I am having is that the amounts need to be entered as (99.95) because the column is using with a mail merge. Ideally it would be nice to enter the number as -99.95 or 14 but the amount on the mail merge wouldn't show as (99.95) or 14.00. I tried to convert the format to number and account with () but when the mail merge pulls the data the format is incorrect.
Secondly I have other tabs labelled Jan, Feb, Mar, Etc for each month.
the data looks similar to this...
<colgroup><col><col><col span="4"><col><col><col span="3"><col span="2"></colgroup><tbody>
</tbody>
Each column is labeled for each day of the month, and the rows are labelled the names. I'm trying to determine if there is a formula that I can use to lookup the date and the name and return the amount. For example on 1/4/15, Larry had a variance of (25.67). The last step would be incorporating some way to change the numbers to work with the mail merge. Alternatively, is there a way to change how the mail merge interprets the data.
One problem I see is the formula would have to work across tabs. For instance Larry on 1/4/15 would be on a different tab then 2/19/15 and 3/2/15.
The is the tab in which the formula would be entered.
| Date | Name | Variance |
| 02/27/15 | Mike | (99.95) |
| 02/27/15 | Beth | (11.07) |
| 02/28/15 | Ashley | (9.76) |
| 02/28/15 | Peter | 14.00 |
| 02/28/15 | Henry | (14.00) |
| 02/28/15 | Diane | 17.00 |
| 02/28/15 | Chris | (5.01) |
| 02/28/15 | Jessica | 106.00 |
<colgroup><col><col><col><col><col><col><col><col><col span="6"></colgroup><tbody>
</tbody>
The first problem I am having is that the amounts need to be entered as (99.95) because the column is using with a mail merge. Ideally it would be nice to enter the number as -99.95 or 14 but the amount on the mail merge wouldn't show as (99.95) or 14.00. I tried to convert the format to number and account with () but when the mail merge pulls the data the format is incorrect.
Secondly I have other tabs labelled Jan, Feb, Mar, Etc for each month.
the data looks similar to this...
| Name | DEPARTMENT | 1/1/2015 | 1/2/2015 | 1/3/2015 | 1/4/2015 | 1/5/2015 | 1/6/2015 | 1/7/2015 | 1/8/2015 | 1/9/2015 | 1/10/2015 | 1/11/2015 |
| David | BAR | |||||||||||
| Glenn | BAR | |||||||||||
| Eric | BAR | |||||||||||
| Larry | BAR | (25.67) | 15.66 | (28.10) | ||||||||
| Steven | BAR | |||||||||||
| Chris | BAR |
<colgroup><col><col><col span="4"><col><col><col span="3"><col span="2"></colgroup><tbody>
</tbody>
Each column is labeled for each day of the month, and the rows are labelled the names. I'm trying to determine if there is a formula that I can use to lookup the date and the name and return the amount. For example on 1/4/15, Larry had a variance of (25.67). The last step would be incorporating some way to change the numbers to work with the mail merge. Alternatively, is there a way to change how the mail merge interprets the data.
One problem I see is the formula would have to work across tabs. For instance Larry on 1/4/15 would be on a different tab then 2/19/15 and 3/2/15.
