| In my worksheet I have a summary table like this: | ||||||||||||||||
| SUMMARY TABLE | ||||||||||||||||
| A | B | C | D | E | ||||||||||||
| 1 | PERIOD | 1 | Jan-18 | |||||||||||||
| 2 | ||||||||||||||||
| 3 | Cust # | BAL B/F | SALES | RETURNS | W/OFFS | |||||||||||
| 4 | A001 | |||||||||||||||
| 5 | A002 | |||||||||||||||
| 6 | A003 | |||||||||||||||
| B1=value selected from a dropdown, here representing January. | ||||||||||||||||
| I also have a table with formulas that calculate the values that must go into col B-E of the summary table. |
<colgroup><col width="71" span="17" style="width: 53pt;"></colgroup><tbody>
</tbody>
| FORMULA TABLE | |||||
| A | B | C | D | E | |
| 1 | 1 | 2 | 3 | 4 | |
| 2 | BAL B/F | =IF(INDEX(DATA!$A$11:$P$87,MATCH($A$7,DATA!A$11:$A$87,0),1)=$A$7,OFFSET(DATA!$P$11,0,(-DATA!$P$8)),"Error") | |||
| 3 | SALES | =IF(INDEX(DATA!$R$11:$AG$87,MATCH($A$7,DATA!$R$11:$R$87,0),1)=$A$7,OFFSET(DATA!$AG$11,0,(-DATA!$P$8)),"Error") | |||
| 4 | RETURNS | =IF(INDEX(DATA!$AI$11:$AX$87,MATCH($A$7,DATA!$AI$11:$AI$87,0),1)=$A$7,OFFSET(DATA!$AX$11,0,(-DATA!$AX$8)),"Error") | |||
| 5 | W/OFFS | =IF(INDEX(DATA!$AZ$11:$BO$87,MATCH($A$7,DATA!$AZ$11:$AZ$87,0),1)=$A$7,OFFSET(DATA!$BO$11,0,(-DATA!$BO$8)),"Error") |
<colgroup><col width="73" span="2" style="width: 55pt;"><col width="431" style="width: 323pt;"><col width="73" span="3" style="width: 55pt;"></colgroup><tbody>
</tbody>
| I want to know if it's possible to update the summary table with the correct result based on the formulas in the formula table, for each customer listed in col A everytime a new value is selected | ||||||||||||||||
| in B1. | ||||||||||||||||
| In this case the formulas in col B of the formula table would have to be applied in te summary table because B1=1 in the summary. |
<colgroup><col width="71" span="17" style="width: 53pt;"></colgroup><tbody>
</tbody>
