Help please - COUNTA error

richtheprojectguy

New Member
Joined
Jul 17, 2023
Messages
2
Office Version
  1. 365
Platform
  1. 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", "")))
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Don't you simply need to point your various COUNTIF's to the cells without the checkboxes? (It's not clear what you want to happen depending on whether these are TRUE/FALSE).

(But if I saw someone using a doozy formula like this, I'd tell them to improve the workbook layout. This is going to be a nightmare for you to maintain. What happens when someone wants to add Product D!)
 
Upvote 0

Forum statistics

Threads
1,224,271
Messages
6,177,620
Members
452,786
Latest member
k3calloway

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top