B5rocksass
Board Regular
- Joined
- Jan 10, 2017
- Messages
- 56
- Office Version
- 2016
- Platform
- Windows
I am working on a template that will autocalculate the price when a specific quantity is entered (after rounding the quantity to the nearest skid size first). Ideally, I'd like to move the prices to a different tab or hide them, so the template only shows only the retrieved price. Please assist.
Ox Paper BD 20210112.xlsx | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | |||
1 | HOW IT IS NOW | Unit Price by Qty Tier | ||||||||||
2 | Qty | UOM | Item No | Qty Per Skid | Final Qty | 500 | 750 | 1000 | 1500 | SubTotal | ||
3 | 500 | EA | ITEM 1 | 200 | 600 | $ 40.50 | $ 33.83 | $ 30.50 | $ 27.17 | $ 24,300.00 | ||
4 | 1,300 | EA | ITEM 2 | 156 | 1404 | $ 1.29 | $ 1.22 | $ 1.19 | $ 1.15 | $ 1,670.76 | ||
5 | ||||||||||||
6 | IDEAL | |||||||||||
7 | Qty | UOM | Item No | Qty Per Skid | Final Qty | Unit Price | SubTotal | |||||
8 | 500 | EA | ITEM 1 | 200 | 600 | $ 40.50 | $ 24,300.00 | |||||
9 | 1,300 | EA | ITEM 2 | 156 | 1404 | $ 1.19 | $ 1,670.76 | |||||
Sheet3 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E8:E9,E3:E4 | E3 | =D3*(ROUNDUP(A3/D3,0)) |
J3 | J3 | =E3*F3 |
J4 | J4 | =E4*H4 |
G8:G9 | G8 | =E8*F8 |