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) |