Not really a question . . . . . . . . . .

[just_jon]

I found out about V here.

 

[Felix Atagong]

Public Function V(Optional vrnt As Variant) As Variant

That V function really gave me an Excelgasm - wow - gonna use it often if my wife permits...

 

[Fergus]

I used this formula to convert numbers to words by referring to "number texts" in a lookup table. That was before I discovered vba versions. A tender we submitted required the rates for all items to be written in words and there were over 2000 items!

=IF(G15=0,"Nil",IF(LEN(G15)>6,VLOOKUP(VALUE(LEFT(G15,(LEN(G15)-6))),Numbers!$A$1:$J$999,10,FALSE)&IF(VALUE(MID(G15,LEN(G15)-5,3))>0,VLOOKUP(VALUE(MID(G15,LEN(G15)-5,3)),Numbers!$A$1:$H$999,8,FALSE),)&IF(AND(VALUE(RIGHT(G15,3))>0,VALUE(RIGHT(G15,3))<100),"and "&VLOOKUP(VALUE(RIGHT(G15,3)),Numbers!$A$1:$F$999,6,FALSE),IF(VALUE(RIGHT(G15,3))=0,,VLOOKUP(VALUE(RIGHT(G15,3)),Numbers!$A$1:$F$999,6,FALSE))),IF(LEN(G15)>3,VLOOKUP(VALUE(LEFT(G15,(LEN(G15)-3))),Numbers!$A$1:$H$999,8,FALSE)&IF(VALUE(RIGHT(G15,3))>0,IF(VALUE(RIGHT(G15,3))<100,"and "&VLOOKUP(VALUE(RIGHT(G15,3)),Numbers!$A$1:$F$999,6,FALSE),VLOOKUP(VALUE(RIGHT(G15,3)),Numbers!$A$1:$F$999,6,FALSE)),""),IF(LEN(G15)<4,IF(VALUE(G15)>0,IF(VALUE(G15)<100,VLOOKUP(VALUE(G15),Numbers!$A$1:$F$999,6,FALSE),VLOOKUP(VALUE(G15),Numbers!$A$1:$F$999,6,FALSE)),""))))&"Yen")

 

[Smitty]

A tender we submitted required the rates for all items to be written in words and there were over 2000 items!

Reminds me of the Guiness commercial I saw recently: "Brilliant, just brilliant!"

Smitty

 

[colcol]

Greetings,

A number of years ago a former boss called me to prepare a spreadsheet that he needed to analyze certain aspects of a large project he was involved with. I was fairly new to Excel at the time and pulled my hair out trying to figure out how to get the workbook to do what they needed. To make a long story short, he called me several weeks later to inform me that they saved over 1.2 million dollars with file. Needless to say that sparked more interest for me into the wild and wonderful world of Excel.

Alan

 

[Greg Truby]

Just in case anyone needs to know -- the V() function that Smitty & Jon referred to can be nested and still work properly, i.e. you can write

=IF(ISNA(v(VLOOKUP(Args1))),IF(ISNA(v(VLOOKUP(Args2))),IF(ISNA(v(VLOOKUP(Args3))),"Not Found",v()),v()),v())

and it will return the proper values. What I found interesting was that if I set a breakpoint in there and step through the recursions the call stack does not augment, each nested step runs as an independent call to the function. Makes sense I suppose since nested built-in's get treated independently. Learned more from that simple little function than looking at the humdingers we all posted. A real gem boys, thanks again for mentioning it Smitty and for posting the code, Jon.

 

[tactps]

I'm shocked and stunned at you all - I thought the idea was to shorten formulas (lol).

Especially you Smitty - with an "OFFSET" - I'm truly disillusioned.

I think I might crawl into a ball until nightfall.

 

[Smitty]

I'm shocked and stunned at you all - I thought the idea was to shorten formulas (lol).

Especially you Smitty - with an "OFFSET" - I'm truly disillusioned.

Ahhhh, git stuffed! Wasn't this a post about the most outrageous one?

A year later, I "could" probably figure out a way to make that monster work without making the WB any bigger, but why? It works like champ, and I'm happy to have my hands out of that **** thing anyway! Freaking needy sales people! (And of course their one person for this office who has access to the wb is in Spain unexpectedly, so guess who else has access to read/write? So I'm in it again!) AAAAAGH! The sad thing is after I built that one summary sheet (note I only posted 1 formula!), I was pretty much the only one to EVER look at the results! So I proved, once again that, just because you can prove it's possible when "they" (the ubiquitious "they") ask if it is, the same "they" don't use it, but the good thing is that the same "they" signed my paycheck this week.

Although, I think we should be nice and be happy that Aladin hasn't reduced all of those to LEN<100 (just for fun of course).

See ya,

Smitty

How do you "unexpectedly" find yourself in Spain? I mean Tijuana from San Diego I can understand; hell, I've woken up on the wrong side of the border when I lived in Texas, but Spain?

 

[tactps]

Your feathers are too easy to ruffle, Smitty!

Keep well, buddy! :wink:

 

[Smitty]

Your feathers are too easy to ruffle

Fair Dinkum, you know me better than that, Mate!

Smitty