Hi All,
I've gotten this far now I'm stuck on this one.
I have an entry form that has a combo box in column B & C (which I have removed and replaced with hard text for forum).
The specific combo box no is placed in column D.
Column E is the overall height and column's F & G are gaps to be deducted. Top gap = 2.5, Bottom gap = 0, Mid Gap = 2.5.
For example:-
when a 2 drawer is selected in combo box this specific No 49 is looking up/matching the result in the drawer fronts worksheet matrix which has all the calculations.
when a 3 drawer is selected in combo box this specific No 50 is looking up/matching the result in the drawer fronts worksheet matrix which has all the calculations.
and so on......
It all works great so when a different combo box item is selected say a 6 drawer the row calculations will change to suit.
Noting - there can be other combo box items selected out of the match range which there is no result needed in columns I - N.
The issue I'm having is I want the user to be able to override the formulas in entry form columns I, J, K etc...and the remaining sizes will change.
Example:-
3 drawer
Currently
I = 264.17
J = 264.17
K = 264.17
Gaps: 800-2.5-2.5-2.5 = 712.5
User inputs 150 in column H
I = 150
J = 281.25
K = 281.25
Gaps: 800-2.5-2.5-2.5 = 712.5
User inputs 150 in column H
User inputs 150 in column I
I = 150
J = 150
K = 412.5
Gaps: 800-2.5-2.5-2.5 = 712.5
If the above user entries are placed directly in the 'drawer front' worksheet it all works fine. However, I wanted the change to happen in the entry form worksheet but I'm not sure if it can be done like this. I was planning on the drawer front worksheet to be hidden from user with all calculations.
I've gotten this far now I'm stuck on this one.
I have an entry form that has a combo box in column B & C (which I have removed and replaced with hard text for forum).
The specific combo box no is placed in column D.
Column E is the overall height and column's F & G are gaps to be deducted. Top gap = 2.5, Bottom gap = 0, Mid Gap = 2.5.
For example:-
when a 2 drawer is selected in combo box this specific No 49 is looking up/matching the result in the drawer fronts worksheet matrix which has all the calculations.
when a 3 drawer is selected in combo box this specific No 50 is looking up/matching the result in the drawer fronts worksheet matrix which has all the calculations.
and so on......
It all works great so when a different combo box item is selected say a 6 drawer the row calculations will change to suit.
Noting - there can be other combo box items selected out of the match range which there is no result needed in columns I - N.
The issue I'm having is I want the user to be able to override the formulas in entry form columns I, J, K etc...and the remaining sizes will change.
Example:-
3 drawer
Currently
I = 264.17
J = 264.17
K = 264.17
Gaps: 800-2.5-2.5-2.5 = 712.5
User inputs 150 in column H
I = 150
J = 281.25
K = 281.25
Gaps: 800-2.5-2.5-2.5 = 712.5
User inputs 150 in column H
User inputs 150 in column I
I = 150
J = 150
K = 412.5
Gaps: 800-2.5-2.5-2.5 = 712.5
If the above user entries are placed directly in the 'drawer front' worksheet it all works fine. However, I wanted the change to happen in the entry form worksheet but I'm not sure if it can be done like this. I was planning on the drawer front worksheet to be hidden from user with all calculations.
| Book1 | ||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | |||
| 52 | Line No | Cabinet Type | Combo Box # | Height | Drawer Fronts Height | |||||||||||
| 53 | (Excludes Kick/Top) | Top Gap | Bot Gap | Mid Gaps | Top Drw | 02nd Drw | 03rd Drw | 04th Drw | 05th Drw | 06th Drw | ||||||
| 54 | 1 | 1 drawer base | 46 | 800 | 2.5 | 0 | 2.5 | 797.5 | 0 | 0 | 0 | 0 | 0 | |||
| 55 | 2 | 2 drawer base | 49 | 800 | 2.5 | 0 | 2.5 | 397.5 | 397.5 | 0 | 0 | 0 | 0 | |||
| 56 | 3 | 3 drawer base | 50 | 800 | 2.5 | 0 | 2.5 | 264.17 | 264.17 | 264.16 | 0 | 0 | 0 | |||
| 57 | 4 | 4 drawer base | 51 | 800 | 2.5 | 0 | 2.5 | 197.5 | 197.5 | 197.5 | 197.5 | 0 | 0 | |||
Entry Form | ||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| F54:F57 | F54 | =IF($E54>0,$F$25,"") |
| G54:G57 | G54 | =IF($E54>0,$M$26,"") |
| H54:H57 | H54 | =IF($E54>0,$F$29,"") |
| I54:I57 | I54 | =IFERROR(INDEX('Drawer Fronts'!$H$3:$H$98,MATCH($D54,'Drawer Fronts'!$A$3:$A$98,0)),"") |
| J54:J57 | J54 | =IFERROR(INDEX('Drawer Fronts'!$I$3:$I$98,MATCH($D54,'Drawer Fronts'!$A$3:$A$98,0)),"") |
| K54:K57 | K54 | =IFERROR(INDEX('Drawer Fronts'!$J$3:$J$98,MATCH($D54,'Drawer Fronts'!$A$3:$A$98,0)),"") |
| L54:L57 | L54 | =IFERROR(INDEX('Drawer Fronts'!$K$3:$K$98,MATCH($D54,'Drawer Fronts'!$A$3:$A$98,0)),"") |
| M54:M57 | M54 | =IFERROR(INDEX('Drawer Fronts'!$L$3:$L$98,MATCH($D54,'Drawer Fronts'!$A$3:$A$98,0)),"") |
| N54:N57 | N54 | =IFERROR(INDEX('Drawer Fronts'!$M$3:$M$98,MATCH($D54,'Drawer Fronts'!$A$3:$A$98,0)),"") |
| Book1 | |||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | |||
| 1 | BASE DRAWER BANKS | HEIGHT | Doors / Drawer Gaps | Drawer Fronts Height | |||||||||||
| 2 | Top Gap | Bot Gap | Mid Gaps | Top Drw | 02nd Drw | 03rd Drw | 04th Drw | 05th Drw | 06th Drw | ||||||
| 3 | 46 | 1 Drawer Base | 800 | 2.5 | 0 | 2.5 | 797.5 | ||||||||
| 4 | 49 | 2 Drawer Base | 800 | 2.5 | 0 | 2.5 | 397.5 | 397.5 | |||||||
| 5 | 50 | 3 Drawer Base | 800 | 2.5 | 0 | 2.5 | 264.17 | 264.17 | 264.16 | ||||||
| 6 | 51 | 4 Drawer Base | 800 | 2.5 | 0 | 2.5 | 197.5 | 197.5 | 197.5 | 197.5 | |||||
Drawer Fronts | |||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| C3:C6 | C3 | =IFERROR(INDEX('Entry Form1'!$E$54:$E$83,MATCH($A3,'Entry Form1'!$D$54:$D$83,0)),0) |
| D3:D6 | D3 | ='Entry Form1'!$M$25 |
| E3:E6 | E3 | ='Entry Form1'!$M$26 |
| F3:F6 | F3 | ='Entry Form1'!$M$29 |
| H3 | H3 | =IF(C3>0,C3-D3-E3,0) |
| H4 | H4 | =ROUND(IF($C4>0,($C4-$D4-$E4-$F4)/2,0),2) |
| I4 | I4 | =ROUND(IF(C4>0,($C4-$D4-$E4-$F4-$H4),0),2) |
| H5 | H5 | =ROUND(IF($C5>0,($C5-$D5-$E5-($F5*2))/3,0),2) |
| I5 | I5 | =ROUND(IF($C5>0,($C5-$D5-$E5-($F5*2)-$H5)/2,0),2) |
| J5 | J5 | =ROUND(IF($C5>0,($C5-$D5-$E5-($F5*2)-$H5-$I5),0),2) |
| A3 | A3 | =Products!A49 |
| A4:A6 | A4 | =Products!A52 |
| H6 | H6 | =ROUND(IF($C6>0,($C6-$D6-$E6-($F6*3))/4,0),2) |
| I6 | I6 | =ROUND(IF($C6>0,($C6-$D6-$E6-($F6*3)-$H6)/3,0),2) |
| J6 | J6 | =ROUND(IF($C6>0,($C6-$D6-$E6-($F6*3)-$H6-$I6)/2,0),2) |
| K6 | K6 | =ROUND(IF($C6>0,($C6-$D6-$E6-($F6*3)-$H6-$I6-$J6),0),2) |
