PDA

View Full Version : Solved: How does this work?



blackie42
06-02-2008, 04:54 AM
I have been sent a cheque request form which has some code in it. Unfortunately its PW protected and no-one seems to know it.

I'm interested in how it works but obviously can't see the code. What happens is that you type a number in one cell say '?5.50' and in the cell below it converts it to words 'Five Pounds and fifty pence'. is this something to do with val(text) conversion?

thanks

Jon

Bob Phillips
06-02-2008, 05:27 AM
See http://www.xldynamic.com/source/xld.xlFAQ0004.html

blackie42
06-02-2008, 06:13 AM
Thanks for the link - 'spellnumber' table is really good.

How do I get it to display Pounds and Pence. I have replaced the references to dollars and cents but when I use the function it still displays $ & cents. The formulas are way ahead of anything I've used.(probably light years in fact!)

regards

jon

Bob Phillips
06-02-2008, 06:29 AM
You have to redefine the defined name Cy to

=IF(INT(!A21)=0,"zero","")&" pound"&IF(INT(!A21)<>1,"s","")&" and "&IF(--_1F>0,_2F&" pence","no pence")

assuming that A21 is your activecell.

blackie42
06-02-2008, 09:21 AM
Sorry XLD - might be being a bit thick but I replaced the formula with the one you posted but when I put a number in column A say in A35 & put
=spellnumber in cell B35 it still comes up with $ & Cents.

Bob Phillips
06-02-2008, 09:36 AM
I misled you a little, I should have said assuming that B21 is your activecell. Other tha that it works fine.

blackie42
06-02-2008, 01:17 PM
Sorry I still don't get it - what do you mean by B21 is your activecell?

I replaced the dollars/cents with Pounds and Pence in B18. I then put a nuber in A35 and formula - upper(spellnumber) in b35 - but it still converts to dollars & cents.

Any chance you cold upload the sheet with it working (or isn't it allowed)

Sorry to be so stupid

Bob Phillips
06-02-2008, 01:53 PM
No, not the value in the cell, the defined name.

Select cell B21, goto Inser>Name>Define..., and scroll through the list until you get the tem Cy, select that, that insert that vale in the RefersTo box.

blackie42
06-02-2008, 02:42 PM
Nope - still don't work - although it doesn't seem to hold the refers to. I did what you said and tried it - didnt work - so went back and it had gone back to what it was.

Thanks for trying

Jon

Bob Phillips
06-02-2008, 02:49 PM
Well it works here.

figment
06-02-2008, 03:00 PM
if the workbook is password protected then that feature may also be locked.

blackie42
06-02-2008, 03:10 PM
Must be doing something wrong - thanks anyway

Jon

Bob Phillips
06-02-2008, 04:37 PM
.

blackie42
06-02-2008, 11:38 PM
Thanks very much for upload - will try and work out why I couldn't update it as you have.

regards

Jon