count Re-occurrences

**stevekirk** · 10-10-2006, 02:36 AM

hi, bryan

hope you had a good break

the 2l, 2l/2s in column e MUST ALWAYS be the last 5 charecters in the cell
this is because the field in the main frame has only 23 spaces

therefore ak has right(e2,5) this then unfortuatly leave spaces in front of the ones that say 2L OR 2S then your other formulars dont work unless i remove the spaces manaullly

can we put in another formular to overcome this

thanks

steve

**Bryan Hessey** · 10-10-2006, 08:18 AM

Originally Posted by stevekirk

hi, bryan

hope you had a good break

the 2l, 2l/2s in column e MUST ALWAYS be the last 5 charecters in the cell
this is because the field in the main frame has only 23 spaces

therefore ak has right(e2,5) this then unfortuatly leave spaces in front of the ones that say 2L OR 2S then your other formulars dont work unless i remove the spaces manaullly

can we put in another formular to overcome this

thanks

steve

One of two options, you can use Trim(A1) on any cell that has leading or trailing spaces to get the valid data, or, if you are trying to access 5 characters from A1 and it is only 1 character long use A1&" " etc
=Right(" "&A1,5)
or
=Left(A1&" ",5)
etc.

so right("_____"&E2,5)
should work for you. (with 5 spaces)

hth
---

**stevekirk** · 10-10-2006, 09:04 AM

bryan,

thanks

what worked was (trim(right(e2,5)

hopefully that project completed ready for the next

thanks for all your help

i have learnt a lot

steve

**Bryan Hessey** · 10-10-2006, 09:11 AM

Originally Posted by stevekirk

bryan,

thanks

what worked was (trim(right(e2,5)

hopefully that project completed ready for the next

thanks for all your help

i have learnt a lot

steve

Great to see, and good that the whole project is now complete (that was some exercise).

Good luck with your next.

---

**stevekirk** · 10-14-2006, 08:50 AM

hi bryan,

project not completed

i need to be able to put a value in ai 2 depending on the size of the material i am using and if the word biesse is in the routing

the costs for the biesse are in the "cost" sheet and also the plus up depending on the material size

please see attached file

thanks
steve

**Bryan Hessey** · 10-15-2006, 07:01 PM

Originally Posted by stevekirk

hi bryan,

project not completed

i need to be able to put a value in ai 2 depending on the size of the material i am using and if the word biesse is in the routing

the costs for the biesse are in the "cost" sheet and also the plus up depending on the material size

please see attached file

thanks
steve

Steve,

The formula for AI2 is

=AC2*Sheet2!$M$2*IF(AC2<>1,1,VLOOKUP(V2,costs!A$22:B$27,2,TRUE))

but you need to set the Lookup table as per costs!A22:B27

You can put this somewhere as a Named range, or just refer to the cells directly as shown.

I presume that you didn't want costs!e14 changed, but wanted to apply this row-by-row in Sheet1.

Cheers
----