iellingson
New Member
- Joined
- Dec 22, 2009
- Messages
- 14
Looking for some VBA code to run behind the scenes to dynamically refresh certain fields (including themselves) based upon the most recent key entry. An example of this is on row 3 column K I could enter $200 and columns G-L would all refresh to reflect the $200 input, however on the same row I could change column L and enter 15% and it would then change all values in column G-L to reflect the output from that input. Columns K & L technically overlap one is a $ while the other is a %. Example attached.
In the example I have forced column J to look at either column K or L based upon my entry, ideally I would like this to dynamically update based upon which field (K or L) the values were entered in most recently.
Thanks in advance.
In the example I have forced column J to look at either column K or L based upon my entry, ideally I would like this to dynamically update based upon which field (K or L) the values were entered in most recently.
Thanks in advance.
| Book1 | ||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | |||
| 1 | BASE | ADJUSTED | ||||||||||||||||||||
| 2 | Field A | Field B | Field C | Field D | Field E | Field F | Field G | Field H | Field I | Field J | Field K | Field L | ||||||||||
| 3 | 802 | 0.29 | $2.11 | $232.58 | $1,689.85 | $1,922.43 | $2.36 | $1,889.85 | $232.58 | $2,122.43 | $200.00 | 9.42% | I would like to be able to change either column K or column L and have it auto recalculate columns G-L based upon whichever was most recently updated. Hardcoded Static formula shouldn't change with Column K/L Inputs Columns that I want to refresh based upon inputs from Column K & L | |||||||||
| 4 | 787 | 0.29 | $2.26 | $228.23 | $1,774.85 | $2,003.08 | $2.54 | $1,997.41 | $228.23 | $2,225.64 | $222.56 | 10.00% | ||||||||||
| 5 | 261 | 0.29 | $2.69 | $75.69 | $703.09 | $778.78 | $3.08 | $803.09 | $75.69 | $878.78 | $100.00 | 11.38% | ||||||||||
| 6 | 334 | 0.29 | $2.74 | $96.86 | $914.52 | $1,011.38 | $3.04 | $1,014.54 | $96.86 | $1,111.40 | $100.03 | 9.00% | ||||||||||
| 7 | 55 | 0.29 | $5.99 | $15.95 | $329.55 | $345.50 | $6.90 | $379.55 | $15.95 | $395.50 | $50.00 | 12.64% | ||||||||||
| 8 | 198 | 0.29 | $2.96 | $57.42 | $585.09 | $642.51 | $3.36 | $664.50 | $57.42 | $721.92 | $79.41 | 11.00% | ||||||||||
| 9 | 253 | 0.29 | $2.73 | $73.37 | $691.05 | $764.42 | $3.13 | $791.05 | $73.37 | $864.42 | $100.00 | 11.57% | ||||||||||
| 10 | 614 | 0.29 | $2.41 | $178.06 | $1,478.06 | $1,656.12 | $2.64 | $1,622.07 | $178.06 | $1,800.13 | $144.01 | 8.00% | ||||||||||
| 11 | 221 | 0.29 | $2.68 | $64.09 | $591.48 | $655.57 | $3.04 | $671.48 | $64.09 | $735.57 | $80.00 | 10.88% | ||||||||||
| 12 | 541 | 0.29 | $2.09 | $156.89 | $1,130.09 | $1,286.98 | $2.24 | $1,212.24 | $156.89 | $1,369.13 | $82.15 | 6.00% | ||||||||||
Sheet1 | ||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| D3, D12, D11, D10, D9, D8, D7, D6, D5, D4 | D3 | =A3*B3 |
| E3, E12, E11, E10, E9, E8, E7, E6, E5, E4 | E3 | =C3*A3 |
| F3, F12, F11, F10, F9, F8, F7, F6, F5, F4 | F3 | =SUM(D3:E3) |
| G3, G12, G11, G10, G9, G8, G7, G6, G5, G4 | G3 | =H3/A3 |
| H3, H12, H11, H10, H9, H8, H7, H6, H5, H4 | H3 | =J3-I3 |
| I3, I12, I11, I10, I9, I8, I7, I6, I5, I4 | I3 | =A3*B3 |
| J3, J11, J9, J7, J5 | J3 | =F3+K3 |
| L3, L11, L9, L7, L5 | L3 | =K3/J3 |
| J4, J12, J10, J8, J6 | J4 | =(F4/(1-L4)) |
| K4, K12, K10, K8, K6 | K4 | =J4-F4 |
