richtheprojectguy
New Member
- Joined
- Jul 17, 2023
- Messages
- 2
- Office Version
- 365
- Platform
- Windows
Hi everyone.
I'm currently building a new order form for work and I've been asked to have one of the tabs auto-concatenate certain information from three other tabs - to prevent the customer from overtyping and getting it wrong.
So I have four tabs:- Product A, Product B, Product C and Control.
The three product tabs each have certain options on them and what I'd like is for the control sheet to have the following options pre-filled depending on what's been selected:
Length
Width
Type
Finish
Coating
Colour
I've tried the code below and what it is doing is always showing the value of PRODUCT A - regardless of whether or not anything has been checked/filled in.
The rest of it seems to work fine - I've got fields for the other's but it's the first option where i want it to tell me which product they chose at the start.
I'm having to be extremely careful as we deal with information I'm not allowed to share so I've had to use these examples.
So the result is, with the below formula in the cell, it reads PRODUCT A all the time. Some of the options are checkboxes and some are fields that can be filled in. I think its the fact I've got boolean
checkboxes that report a FALSE unless checked. It's this FALSE that I believe is "tripping" the formula into thinking there's something been filled in. How do I get round this?
=IF(OR('PRODUCT A'!E13<>"" , 'PRODUCT A'!E14<>"" , 'PRODUCT A'!E15<>"" , 'PRODUCT A'!E17<>"" ,
COUNTIF('PRODUCT A'!G26:G28, "<>") > 0, COUNTIF('PRODUCT A'!J26:J28, "<>") > 0,
COUNTIF('PRODUCT A'!M26:M28, "<>") > 0, COUNTIF('PRODUCT A'!P26:P27, "<>") > 0,
COUNTIF('PRODUCT A'!O43:O45, "<>") > 0, 'PRODUCT A'!O46<>"" ,
'PRODUCT A'!O47<>"" , COUNTIF('PRODUCT A'!O48:O49, "<>") > 0,
COUNTIF('PRODUCT A'!O52:O54, "<>") > 0, 'PRODUCT A'!O55<>"" ,
'PRODUCT A'!O57<>0), "PRODUCT A",
IF(COUNTA('PRODUCT B'!E13:E15) + COUNTA('PRODUCT B'!E17) +
COUNTA('PRODUCT B'!G19:G21) + COUNTA('PRODUCT B'!J19:J21) +
COUNTA('PRODUCT B'!M19:M21) + COUNTA('PRODUCT B'!Q19:Q20) +
COUNTA('PRODUCT B'!P32:P34) + COUNTA('PRODUCT B'!P35) +
COUNTA('PRODUCT B'!P36) + COUNTA('PRODUCT B'!P37:P38) +
COUNTA('PRODUCT B'!P41:P43) + COUNTA('PRODUCT B'!P44) +
COUNTA('PRODUCT B'!P46) > 0, "PRODUCT B",
IF(COUNTA('PRODUCT C'!E13:E14) + COUNTA('PRODUCT C'!E16) +
COUNTA('PRODUCT C'!G25:G27) + COUNTA('PRODUCT C'!J25:J27) +
COUNTA('PRODUCT C'!M25:M26) +
COUNTA('PRODUCT C'!P25:P26) +
COUNTA('PRODUCT C'!O45:O47) +
COUNTA('PRODUCT C'!O48:O50) +
COUNTA('PRODUCT C'!O53:O55) +
COUNTA('PRODUCT C'!O56) +
COUNTA('PRODUCT C'!O58) > 0, "PRODUCT C", "")))
I'm currently building a new order form for work and I've been asked to have one of the tabs auto-concatenate certain information from three other tabs - to prevent the customer from overtyping and getting it wrong.
So I have four tabs:- Product A, Product B, Product C and Control.
The three product tabs each have certain options on them and what I'd like is for the control sheet to have the following options pre-filled depending on what's been selected:
Length
Width
Type
Finish
Coating
Colour
I've tried the code below and what it is doing is always showing the value of PRODUCT A - regardless of whether or not anything has been checked/filled in.
The rest of it seems to work fine - I've got fields for the other's but it's the first option where i want it to tell me which product they chose at the start.
I'm having to be extremely careful as we deal with information I'm not allowed to share so I've had to use these examples.
So the result is, with the below formula in the cell, it reads PRODUCT A all the time. Some of the options are checkboxes and some are fields that can be filled in. I think its the fact I've got boolean
checkboxes that report a FALSE unless checked. It's this FALSE that I believe is "tripping" the formula into thinking there's something been filled in. How do I get round this?
=IF(OR('PRODUCT A'!E13<>"" , 'PRODUCT A'!E14<>"" , 'PRODUCT A'!E15<>"" , 'PRODUCT A'!E17<>"" ,
COUNTIF('PRODUCT A'!G26:G28, "<>") > 0, COUNTIF('PRODUCT A'!J26:J28, "<>") > 0,
COUNTIF('PRODUCT A'!M26:M28, "<>") > 0, COUNTIF('PRODUCT A'!P26:P27, "<>") > 0,
COUNTIF('PRODUCT A'!O43:O45, "<>") > 0, 'PRODUCT A'!O46<>"" ,
'PRODUCT A'!O47<>"" , COUNTIF('PRODUCT A'!O48:O49, "<>") > 0,
COUNTIF('PRODUCT A'!O52:O54, "<>") > 0, 'PRODUCT A'!O55<>"" ,
'PRODUCT A'!O57<>0), "PRODUCT A",
IF(COUNTA('PRODUCT B'!E13:E15) + COUNTA('PRODUCT B'!E17) +
COUNTA('PRODUCT B'!G19:G21) + COUNTA('PRODUCT B'!J19:J21) +
COUNTA('PRODUCT B'!M19:M21) + COUNTA('PRODUCT B'!Q19:Q20) +
COUNTA('PRODUCT B'!P32:P34) + COUNTA('PRODUCT B'!P35) +
COUNTA('PRODUCT B'!P36) + COUNTA('PRODUCT B'!P37:P38) +
COUNTA('PRODUCT B'!P41:P43) + COUNTA('PRODUCT B'!P44) +
COUNTA('PRODUCT B'!P46) > 0, "PRODUCT B",
IF(COUNTA('PRODUCT C'!E13:E14) + COUNTA('PRODUCT C'!E16) +
COUNTA('PRODUCT C'!G25:G27) + COUNTA('PRODUCT C'!J25:J27) +
COUNTA('PRODUCT C'!M25:M26) +
COUNTA('PRODUCT C'!P25:P26) +
COUNTA('PRODUCT C'!O45:O47) +
COUNTA('PRODUCT C'!O48:O50) +
COUNTA('PRODUCT C'!O53:O55) +
COUNTA('PRODUCT C'!O56) +
COUNTA('PRODUCT C'!O58) > 0, "PRODUCT C", "")))