Ask any queries related to excel.
CA R K SINGH (CA CS FINAL CMA INTER) (372 Points)
05 August 2011Ask any queries related to excel.
CA J.Alamelu lakshmi
(Chartered Accountant)
(852 Points)
Replied 05 August 2011
How to select alternate cells ??
CA R K SINGH
(CA CS FINAL CMA INTER)
(372 Points)
Replied 05 August 2011
Alternate cells can be selected by Presing Ctrl key & Click on the cell which you want to select by mouse
CA J.Alamelu lakshmi
(Chartered Accountant)
(852 Points)
Replied 05 August 2011
But how to select without using mouse? I learnt abt it sumwer but forgot now..
CA R K SINGH
(CA CS FINAL CMA INTER)
(372 Points)
Replied 05 August 2011
i. Press Shift-F8 key to anchor the cursor. Sentence "Add to selection" will appear in status bar.
ii. Select desired ranges.
iii. and when you're done, press Esc to toggle off the option of non-contiguous selection.
Pankaj Arora
(Job)
(150 Points)
Replied 05 August 2011
There is an option in excel 2007 that when we type in numericals it automaticaly converts the numbers in bath language, can it also be done in english language?
Kaushik
(Professional)
(549 Points)
Replied 05 August 2011
Originally posted by : Pankaj Arora | ||
There is an option in excel 2007 that when we type in numericals it automaticaly converts the numbers in bath language, can it also be done in english language? |
With VBA code or with formula it is possible.
Paste below vba code to VBA module.
Function Ntow (amt As Variant) As Variant
Dim FIGURE As Variant
Dim LENFIG As Integer
Dim i As Integer
Dim WORDs(19) As String
Dim tens(9) As String
WORDs(1)= "One"
WORDs(2)= "Two"
WORDs(3)= "Three"
WORDs(4)= "Four"
WORDs(5)= "Five"
WORDs(6)= "Six"
WORDs(7)= "Seven"
WORDs(8)= "Eight"
WORDs(9)= "Nine"
WORDs(10)= "Ten"
WORDs(11)= "Eleven"
WORDs(12)= "Twelve"
WORDs(13)= "Thirteen"
WORDs(14)= "Fourteen"
WORDs(15)= "Fifteen"
WORDs(16)= "Sixteen"
WORDs(17)= "Seventeen"
WORDs(18)= "Eighteen"
WORDs(19)= "Nineteen"
tens(2) = "Twenty"
tens(3) = "Thirty"
tens(4) = "Fourty"
tens(5) = "Fifty"
tens(6) = "Sixty"
tens(7) = "Seventy"
tens(8) = "Eighty"
tens(9) = "Ninety"
FIGURE = amt
FIGURE = Format(FIGURE,"FIXED")
FIGLEN = Len(FIGURE)
If figlen < 12 Then
FIGURE = Space(12-FIGLEN) & FIGURE
End If
If Val(Left(figure,9)) > 1 Then
Ntow= "Rupees "
Elseif Val(Left(figure,9)) = 1 Then
Ntow = "Rupee "
End If
For i = 1 To 3
If Val(Left(figure,2)) < 20 And Val(Left(figure,2)) > 0 Then
Ntow = Ntow & words(Val(Left(figure,2)))
Elseif Val(Left(figure,2)) > 19 Then
Ntow = Ntow & tens(Val(Left(figure,1)))
Ntow = Ntow & words(Val( Right(Left(figure,2),1) ))
End If
If i = 1 And Val(Left(figure,2)) > 0 Then
Ntow = Ntow & " Crore "
Elseif i = 2 And Val(Left(figure,2)) > 0 Then
Ntow = Ntow & " Lakh "
Elseif i = 3 And Val(Left(figure,2)) > 0 Then
Ntow = Ntow & " Thousand "
End If
figure = Mid(figure,3)
Next i
If Val(Left(figure,1)) > 0 Then
Ntow = Ntow & words(Val(Left(figure,1))) + " Hundred "
End If
figure = Mid(figure,2)
If Val(Left(figure,2)) < 20 And Val(Left(figure,2)) > 0 Then
Ntow = Ntow & words(Val(Left(figure,2)))
Elseif Val(Left(figure,2)) > 19 Then
Ntow = Ntow & tens(Val(Left(figure,1)))
Ntow = Ntow & words(Val( Right(Left(figure,2),1) ))
End If
figure = Mid(figure,4)
If Val(figure) > 0 Then
Ntow = Ntow & " Paise "
If Val(Left(figure,2)) < 20 And Val(Left(figure,2)) > 0 Then
Ntow = Ntow & words(Val(Left(figure,2)))
Elseif Val(Left(figure,2)) > 19 Then
Ntow = Ntow & tens(Val(Left(figure,1)))
Ntow = Ntow & words(Val( Right(Left(figure,2),1) ))
End If
End If
FIGURE = amt
FIGURE = Format(FIGURE,"FIXED")
If Val(figure) > 0 Then
Ntow = Ntow & " Only "
End If
End Function
or you can us\download addins from google search.
see more details for user defined functions & getting started with macros from below page.
https://dmcritchie.mvps.org/excel/getstarted.htm
Below are some usefule pages for converting number to words.
https://exshail.web.officelive.com/Addins.aspx
(for Indian Ruppees Excel Addins.)
https://exshail.web.officelive.com/ComAddins.aspx
(for com-addins (also in hindi words) for excel office application.)
https://exshail.web.officelive.com/Misc.aspx
(for spellnoformula workbook ( no vba code requried)
IndianSpellNoFormulaHindi (for hindi)
Pankaj Arora
(Job)
(150 Points)
Replied 06 August 2011
Thanks for the info but the fact is that I m not a computer professional so if can explain this in a layman language I'll be vvery thankful.
Also, are these option available in 2003 also.
JMD
(STUDENT)
(563 Points)
Replied 06 August 2011
Pls tell me by which formula i can totals of some SUM FORMULAS ONLY. Means using sum, i'll have to the path of each cell required to add. but if there is any formula which i can apply to a rnge of cells, and the return must be only total of totals, where a sum formula has been already inserted.
CA R K SINGH
(CA CS FINAL CMA INTER)
(372 Points)
Replied 06 August 2011
Hi Pankaj Arora I couldn't understand your question. Plz Mail me at helpinghand786 @ in.com . I have an utility which can convert numbers into Text (In English).
CA R K SINGH
(CA CS FINAL CMA INTER)
(372 Points)
Replied 06 August 2011
Hi JMD . I don't think there is any formula for this. But an excel Add in Can be made for your query. iF you require this add in mail me at helpinghand786 @ in.com
Exshail
(Software)
(1576 Points)
Replied 06 August 2011
Originally posted by : JMD | ||
Pls tell me by which formula i can totals of some SUM FORMULAS ONLY. Means using sum, i'll have to the path of each cell required to add. but if there is any formula which i can apply to a rnge of cells, and the return must be only total of totals, where a sum formula has been already inserted. |
Instead of sum formula always use subtotal function. see more details in Excel Help. Also see below image.
Exshail Software
Free Exshail Classic Menu for Excl-2007.
https://exshail.web.officelive.com/Exshail_Classic_Menu.aspx
Kaushik
(Professional)
(549 Points)
Replied 06 August 2011
Originally posted by : Pankaj Arora | ||
Thanks for the info but the fact is that I m not a computer professional so if can explain this in a layman language I'll be vvery thankful. Also, are these option available in 2003 also. |
This options (using excel addins) are also available in 2003.
Don't waorry, try again to read the stuff in web site suggested again & google search Excel Addins to download & installed in excel. Read inbuilt Excel Help first. Previously I was also like you, but learn through internet.
"Koshish Karne walo Ki Har Nahi Hoti"
satya
(Executive Excise)
(1839 Points)
Replied 09 August 2011
Dear Singh Sir,
We want formula for this querry, charactor to be pick up basing on numeric field match,
in sheet1 column "A" having Numeric fields, "B" is Charactors, in sheet 2 column no "a" having numeric field only, in "B" we want to extract with matching of sheet2 col."A" = sheet1 col. "A" to be pick up Col."B" from Sheet1 to sheet2.
Thanks in advance.
Junaid
(Acounting works)
(46 Points)
Replied 10 August 2011
Hello Sir,
I want to learn excel formula for cacluclating income tax, i knows ot before but forgot now,
it is smthing like, if,(A1<=160000,'NIL', if (A1>=160000,'a1-160000*10%.................................... etc etc.
but now forgot the same, it will be very grateful to you if you tell me the formula.
Please
Thanx in Advance