Peter Davison
Active Member
- Joined
- Jun 4, 2020
- Messages
- 435
- Office Version
- 365
- Platform
- Windows
I’ve got this formula that counts the number of attributes in a column which is okay when I knew which column the attribute heading was in, but now it has become variable.
This is my current formula in a cell –
=SUM(IF(ISTEXT('BCP Raw Product'!$E$2:$E$1000),1/COUNTIF('BCP Raw Product'!$E$2:$E$1000, 'BCP Raw Product'!$E$2:$E$1000),""))+3
I have worked out a formula which matches the attribute name and returns me the column address, but when I try and replace in the original formula it does not like it. I am sure it’s down to brackets and quotes.
="'BCP Raw Product'!$"&CHAR(65-1+MATCH($D42,'BCP Raw Product'!$A$1:$Z$1,0))&"$"&2&":"&"$"&CHAR(65-1+MATCH($D42,'BCP Raw Product'!$A$1:$Z$1,0))&"$"&1000
Returns this –
'BCP Raw Product'!$K$2:$K$1000
I then try to replace - 'BCP Raw Product'!$E$2:$E$1000 In the top formula for the 3 entries with the code above but doesn’t like it?
Would you know what I am doing wrong?
Thanks for your help
This is my current formula in a cell –
=SUM(IF(ISTEXT('BCP Raw Product'!$E$2:$E$1000),1/COUNTIF('BCP Raw Product'!$E$2:$E$1000, 'BCP Raw Product'!$E$2:$E$1000),""))+3
I have worked out a formula which matches the attribute name and returns me the column address, but when I try and replace in the original formula it does not like it. I am sure it’s down to brackets and quotes.
="'BCP Raw Product'!$"&CHAR(65-1+MATCH($D42,'BCP Raw Product'!$A$1:$Z$1,0))&"$"&2&":"&"$"&CHAR(65-1+MATCH($D42,'BCP Raw Product'!$A$1:$Z$1,0))&"$"&1000
Returns this –
'BCP Raw Product'!$K$2:$K$1000
I then try to replace - 'BCP Raw Product'!$E$2:$E$1000 In the top formula for the 3 entries with the code above but doesn’t like it?
Would you know what I am doing wrong?
Thanks for your help