Excel at Excel....!!!!!(Ask anything and everything)

hi all,

i tried to do a sudoku in excel, and defined the cell and the validation rule that no cell to take values greater than 9 and less than 1. but then everything derailed. and i could not move further.  can anybody suggest me how to create a sudoku and the links i have to give.

plz reply

WOW!!!! this sure seems to be in demand!!!!!

Convert numbers to words!!!!!

well, my word on it is, YOU CANNOT CONVERT NUMBER TO WORDS USING FORMULAE!!!!!!!.....

you HAVE to use a third party software(eg:ASAP UTILITIES) or write a VBA code!!

PLease let me know if anyone is interested in the VBA code for this function!!

hi Khaleel,

i am interested in this code, u mentioned above.

thanks,

yayati

Hi khaleel,

Hello Sunil,

Please go thru the book i have uploaded in "Files" section,

File is called - "Excel for beginners - Important Basics".

Pivot tables is a very vast subject, after going through it, if you still have any doubts, i would be more than glad to help you out.

Hello sir.

First time i saw this site & also saw somebody having very good knowledge of excel. It's great that you are sharing this knowledge & using it to help others in CA profession. I really appreciate that.

I think i should also contribute over here. So I request you to tell me if there is anything that i can do here.

Thanking You,

Seema Gupta.

Hi Lakshminarayana,

VLOOKUP & HLOOKUP solve 99.99% of cases, the combination on INDEX&MATCH is very rare as it has very few advantages over V/Hlookup.

Comming to your querry, my understanding is that you have a master record of employees on SHEET2 and on SHEET1 you want to retrieve only the MUMBAI EMPLOYESS, right?

In this case i would rather use AUTOFILTERS on SHEET2 and COPY/PASTE the data.

Doing this with VLOOKUP might not give the desired result, because your lookupvalue "mumbai" remains constant, so it will keep giving you the same result over and over.

Please let me know what is that you are trying to achive, its quite possible that VLOOKUP is NOT the right function for it!!!

Hi Naresh,

I am not very sure about your query, but by the looks of it, i think "Find & Replace" function will solve it.

Select the area, which has unwanted commas, press CTRL+F, the menu that popsup will have a replace function, put "," in find section , and leave the replace section blank. This way all your commas will get removed.

Hope this helps.

Hi Friends,

The MOST WANTED CODE, convert number to words........here it goes..

Function ft(n)
n = Int(n)
Dim convert$
convert$ = Trim$(Str(n))
Dim unit$(19)
unit$(1) = "one "
unit$(2) = "two "
unit$(3) = "three "
unit$(4) = "four "
unit$(5) = "five "
unit$(6) = "six "
unit$(7) = "seven "
unit$(8) = "eight "
unit$(9) = "nine "
unit$(10) = "ten "
unit$(11) = "eleven "
unit$(12) = "twelve "
unit$(13) = "thirteen "
unit$(14) = "fourteen "
unit$(15) = "fifteen "
unit$(16) = "sixteen "
unit$(17) = "seventeen "
unit$(18) = "eighteen "
unit$(19) = "nineteen "
Dim ten$(9)
ten$(2) = "twenty "
ten$(3) = "thirty "
ten$(4) = "forty "
ten$(5) = "fifty "
ten$(6) = "sixty "
ten$(7) = "seventy "
ten$(8) = "eighty "
ten$(9) = "ninety "
Dim large$(3)
large$(0) = "hundred "
large$(1) = "thousand "
large$(2) = "million "
Dim result$

'next line is to cater for a length of 1 character
If Len(convert$) = 1 Then convert$ = " " + convert$
If Val(Right(convert$, 2)) > 19 Then
   result$ = unit$(Val(Right(convert$, 1)))
   convert$ = Left(convert$, Len(convert$) - 1)
   result$ = ten$(Val(Right(convert$, 1))) + result$
   convert$ = Left(convert$, Len(convert$) - 1)
Else
   result$ = unit$(Val(Right(convert$, 2)))
   convert$ = Left(convert$, Len(convert$) - 2)
End If
Do While Len(convert$) > 0
   Select Case largecount
   Case 0
       'dealing with hundreds
       If Val(Right(convert$, 1)) > 0 Then result$ = unit$(Val(Right(convert$, 1))) + large$(0) + result$
       convert$ = Left(convert$, Len(convert$) - 1)
       largecount = largecount + 1
   Case 1
       'dealing with thousands
       If Len(convert$) = 1 Then convert$ = " " + convert$
       If Val(Right(convert$, 2)) > 19 Then
           subresult$ = unit$(Val(Right(convert$, 1)))
           convert$ = Left(convert$, Len(convert$) - 1)
           subresult$ = ten$(Val(Right(convert$, 1))) + subresult$
           convert$ = Left(convert$, Len(convert$) - 1)
       Else
          
           subresult$ = unit$(Val(Right(convert$, 2)))
           convert$ = Left(convert$, Len(convert$) - 2)
       End If
       If Len(convert$) > 0 Then
           If Val(Right(convert$, 1)) > 0 Then subresult$ = unit$(Val(Right(convert$, 1))) + large$(0) + subresult$ + large$(1)
               convert$ = Left(convert$, Len(convert$) - 1) ' needs checking to make sure we HAVE a value to deal with
           Else
               subresult$ = subresult$ + large$(1)
           End If
       largecount = largecount + 1
       result$ = subresult$ + result$
   Case 2
   subresult$ = ""
   If Len(convert$) = 1 Then convert$ = " " + convert$
   'dealing with millions
       If Val(Right(convert$, 2)) > 19 Then
           subresult$ = unit$(Val(Right(convert$, 1)))
           convert$ = Left(convert$, Len(convert$) - 1)
           subresult$ = ten$(Val(Right(convert$, 1))) + subresult$
           convert$ = Left(convert$, Len(convert$) - 1)
       Else
           If Len(convert$) = 1 Then convert$ = " " + convert$
           subresult$ = unit$(Val(Right(convert$, 2)))
           convert$ = Left(convert$, Len(convert$) - 2)
       End If
       If Len(convert$) > 0 Then
           If Val(Right(convert$, 1)) > 0 Then subresult$ = unit$(Val(Right(convert$, 1))) + large$(0) + subresult$ + large$(2)
               convert$ = Left(convert$, Len(convert$) - 1) ' needs checking to make sure we HAVE a value to deal with
           Else
               subresult$ = subresult$ + large$(2)
           End If
       largecount = largecount + 1
       result$ = subresult$ + result$

convert$ = "" 'for testing purposes
'this routine will return values from 1 to 999999999 (nine hundred ninety nine million nine hundred ninety nine thousand nine hundred ninety nine)
'it will ONLY return the whole number part but you can deal with decimals manually
'example
'********************************************************************
'=ft(C7) & " dollars and " & ft(ROUND((C7-INT(C7))*100,0))&  " cents"
'where c7 contains the value to be shown as text
'********************************************************************

   End Select
Loop

ft = result$

End Function