Re: Custom Data Validation with multiple column
Thanks,
but it did not work, all the condition in excel is ok but it does not work in data validation,
condition 4 does OK but i want a cell reference instead of "<150000"
Re: Custom Data Validation with multiple column
Thanks,
but it did not work, all the condition in excel is ok but it does not work in data validation,
condition 4 does OK but i want a cell reference instead of "<150000"
Re: SumIf cell is equal to any in a list
Quote from Brewface;689274Display MoreHello all,
I have had no luck with internet searches nor racking my brain for a solution. The basic problem is this, I have a large range of data 2000+ cells and I need to Add only the hours for a division within a department.
The division is given with a list of names. To be more clear here is an exampleName Hours
Doe 2
Smith 3
Murphy 3
Roberts 8
Mollard 4Names belonging to Division A_
Doe
MollardDivision A Total hours:
6
Usually i would just do a series of If statements but there is between 30 and 55 names in one division and this would be painstakingly tedious (I have to do this same thing for 13 divisions...)
Ideally I would just like to Specify my Sum Range, Specify the Criteria Range, Specify the Criteria List. Exactly like a Sumif...but the Criteria can be anyone in a list. I thought of some combination of Vlookup or match might work but have had no luck so far.Thanks for the help,
Brewface
you just use sumifs against sumif. sumifs has the option to add lots of Critia,range and most important thing is that you can easily understand how it works.
thanks
Re: VLOOKUP multiple arguments
=INDEX($C$2:$C$7,MATCH(C11&D11,$A$2:$A$7&$B$2:$B$7,0))
[TABLE="width: 123"]
[TD="class: xl65, width: 64, align: right"]CNT+Shift+Enter
[/TD]
[/TABLE]
Re: Conditional formatting via formula
Select your Range
Go to Conditional Formula-Custom and type
=FIND("-",A1,1)
or
=FIND("-",A1)
That will work
Re: validations for negative values
Select Cell or Whole column,
Go to data Validation select Custom and type this
=$I1>=0
Click Ok
[TABLE="width: 400"]
[tr][td]NO
[/td][td]A
[/td][td]B
[/td][td]C
[/td][td]D
[/td][td]E
[/td][td]F
[/td][td]G
[/td][td]H
[/td][/tr][tr][td]1
[/td][td]111
[/td][td]100000
[/td][td][/td][td][/td][td][/td][td][/td][td]111
[/td][td]450
[/td][/tr][tr][td]2
[/td][td]222
[/td][td]200000
[/td][td][/td][td][/td][td][/td][td][/td][td]222
[/td][td]500
[/td][/tr][tr][td]3
[/td][td]333
[/td][td]300000
[/td][td][/td][td][/td][td][/td][td][/td][td]333
[/td][td]640
[/td][/tr][tr][td]4
[/td][td]444
[/td][td]400000
[/td][td][/td][td][/td][td][/td][td][/td][td]111
[/td][td]687
[/td][/tr][tr][td]5
[/td][td]555
[/td][td]500000
[/td][td][/td][td][/td][td][/td][td][/td][td]444
[/td][td]5690
[/td][/tr][tr][td]6
[/td][td]666
[/td][td]600000
[/td][td][/td][td][/td][td][/td][td][/td][td]555
[/td][td]4569
[/td][/tr][tr][td]7
[/td][td]777
[/td][td]700000
[/td][td][/td][td][/td][td][/td][td][/td][td]111
[/td][td]7890
[/td][/tr][tr][td]8
[/td][td]888
[/td][td]800000
[/td][td][/td][td][/td][td][/td][td][/td][td]999
[/td][td]26460
[/td][/tr][tr][td]9
[/td][td]999
[/td][td]900000
[/td][td][/td][td][/td][td][/td][td][/td][td]777
[/td][td]5426
[/td][/tr][tr][td]10
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]888
[/td][td]2564
[/td][/tr][tr][td]11
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]111
[/td][td]56987
[/td][/tr][tr][td]12
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]111
[/td][td]560
[/td][/tr][tr][td]13
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]222
[/td][td]400
[/td][/tr][tr][td]14
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]111
[/td][td]6980
[/td][/tr][tr][td]15
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]555
[/td][td]578
[/td][/tr][tr][td]16
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]111
[/td][td]300
[/td][/tr][tr][td]17
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr][tr][td]18
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr][tr][td]19
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/TABLE]
[TABLE="width: 70"]
[tr][td]Codition 1=
[TABLE="width: 233"]
[TD="class: xl65, width: 233, colspan: 3"]SUMPRODUCT((G:G=G2)*(H:H))<B2
[/TABLE]
[/TD]
Codition 2=
[TABLE="width: 479"]
[TD="class: xl66, width: 479, colspan: 6"]SUMPRODUCT((G:G=G2)*(H:H))<INDEX($A$2:$E$10,MATCH(G2,G:G,0),2)
[/TABLE]
[/TD]
Codition 3=
[TABLE="width: 479"]
[TD="class: xl66, width: 479, colspan: 6"]SUMPRODUCT((G:G=G2)*(H:H))<VLOOKUP(G2,A:E,2,FALSE)
[/TABLE]
[/TD]
Codition 4=
[TABLE="width: 479"]
[TD="class: xl66, width: 479, colspan: 6"]SUMPRODUCT((G:G=G2)*(H:H))<150000
[/TABLE]
[/TD]
[/TABLE]
[TABLE="width: 896"]
[tr]
[TD="colspan: 4"]I m trying to validate data in Column H.
[/TD]
[TD="colspan: 14"]when I put code in column G and amount In column H, it should not exceed amount 100000 because A2 and B2 validate code and amount[/TD]
[TD="colspan: 3"]reference code 111
[TABLE="width: 512"]
[tr]
[TD="colspan: 8"]Condition 4 did OK but all seems right, I need condition 2 to be right.
[/TD]
[/TABLE]
[/TD]
[/TABLE]
Re: Conditional formatting via formula
when we use left,right mid it refers value to text, so keep " " between criteria if is it a number.
[TABLE="width: 896"]
[tr]
[TD="colspan: 4"]I m trying to validate data in Column H.
[/TD]
[TD="colspan: 14"]when I put code in column G and amount In column H, it should not exceed amount 100000 because A2 and B2 validate code and amount[/TD]
[TD="colspan: 3"]reference code 111[/TD]
[/TABLE]
[TABLE="width: 64"]
[tr][/tr][tr][/tr][tr][td][/td][/tr]
[/TABLE]
Re: More Sum If Help Please
Suppose your two line is column A & B,
just type any cell=SUMIF(A:A,A1,B:B) press enter.
Or,
=SUMIF(A:A,A1,B:B)+SUMIF(A:A,A2,B:B)
"A1 for Mex"
"A2 for Usa".
Re: Indian Currency Number to Word
Sorry I don't blame VBA,
Just try it in excel,
As you better know CNTR+F and "Replace all" your desired cell.
Re: Indian Currency Number to Word
Sorry I don't blame VBA, just try it in excel.
its easy to use, just find and replace your desired cell.
As you better know Cntr+F and replace all( for edit).
Re: Indian Currency Number to Word
Find and replace your selected cell from the excel formula,
=TRIM(CONCATENATE(IFERROR(LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{10},{1}),1),{"0","1","2","3","4","5","6","7","8","9"},{"","One Hundred","Two Hundred","Three Hundred","Four Hundred","Five Hundred","Six Hundred","Seven Hundred","Eight Hundred","Nine Hundred"}),"")," ",IFERROR(IF(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{9,10},{1,2}),1)="1",LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{9,10},{2,3}),1),{"0","1","2","3","4","5","6","7","8","9"},{"Ten","Eleven Crore","Twelve Crore","Thirteen Crore","Fourteen Crore","Fifteen Crore","Sixteen Crore","Seventeen Crore","Eighteen Crore","Nineteen Crore"}),LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{9,10},{1,2}),1),{"0","1","2","3","4","5","6","7","8","9"},{"","","Twenty","Thirty","Forty","Fifty","Sixty","Seventy","Eighty","Ninety"})),"")," ",IFERROR(IF(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{8,9,10},{11,1,2}),1)="1",LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{8,9,10,11},{11,1,2,3}),1),{"0","1","2","3","4","5","6","7","8","9"},{"","","","","","","","","",""}),LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{7,8,9,10},{12,1,2,3}),1),{"0","1","2","3","4","5","6","7","8","9"},{"Crore","One Crore","Two Crore","Three Crore","Four Crore","Five Crore","Six Crore","Seven Crore","Eight Crore","Nine Crore"})),"")," ",IFERROR(IF(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{7,8,9,10},{1,2,3,4}),1)="1",LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{7,8,9,10},{2,3,4,5}),1),{"0","1","2","3","4","5","6","7","8","9"},{"Ten","Eleven Lac","Twelve Lac","Thirteen Lac","Fourteen Lac","Fifteen Lac","Sixteen Lac","Seventeen Lac","Eighteen Lac","Nineteen Lac"}),LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{7,8,9,10},{1,2,3,4}),1),{"0","1","2","3","4","5","6","7","8","9"},{"","","Twenty","Thirty","Forty","Fifty","Sixty","Seventy","Eighty","Ninety"})),"")," ",IFERROR(IF(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{6,7,8,9,10},{11,1,2,3,4}),1)="1",LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{6,7,8,9,10},{11,2,3,4,5}),1),{"0","1","2","3","4","5","6","7","8","9"},{"","","","","","","","","",""}),LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{5,6,7,8,9,10},{11,1,2,3,4,5}),1),{"0","1","2","3","4","5","6","7","8","9"},{"Lac","One Lac","Two Lac","Three Lac","Four Lac","Five Lac","Six Lac","Seven Lac","Eight Lac","Nine Lac"})),"")," ",IFERROR(IF(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{5,6,7,8,9,10},{1,2,3,4,5,6}),1)="1",LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{4,5,6,7,8,9,10},{1,2,3,4,5,6,7}),1),{"0","1","2","3","4","5","6","7","8","9"},{"Ten Thousand","Eleven Thousand","Twelve Thousand","Thirteen Thousand","Forteen Thousand","Fifteen Thousand","Sixteen Thousand","Seventeen Thousand","Eighteen Thousand","Nineteen Thousand"}),LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{5,6,7,8,9,10},{1,2,3,4,5,6}),1),{"0","1","2","3","4","5","6","7","8","9"},{"","","Twenty","Thirty","Forty","Fifty","Sixty","Seventy","Eighty","Ninety"})),"")," ",IFERROR(IF(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{4,5,6,7,8,9,10},{12,1,2,3,4,5,6}),1)="0",LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{4,5,6,7,8,9,10},{1,2,3,4,5,6,7}),1),{"0","1","2","3","4","5","6","7","8","9"},{"","One Thousand","Two Thousand","Three Thousand","Four Thousand","Five Thousand","Six Thousand","Seven Thousand","Eight Thousand","Nine Thousand"}),IF(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{4,5,6,7,8,9,10},{12,1,2,3,4,5,6}),1)="1",LOOKUP(MID(TEXT(A12,0),3,1),{"0","1","2","3","4","5","6","7","8","9"},{"","","","","","","","","",""}),LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{4,5,6,7,8,9,10},{1,2,3,4,5,6,7}),1),{"0","1","2","3","4","5","6","7","8","9"},{"Thousand","One Thousand","Two Thousand","Three Thousand","Four Thousand","Five Thousand","Six Thousand","Seven Thousand","Eight Thousand","Nine Thousand"}))),"")," ",IFERROR(LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{3,4,5,6,7,8,9,10},{1,2,3,4,5,6,7,8,9}),1),{"0","1","2","3","4","5","6","7","8","9"},{"","One Hundred","Two Hundred","Three Hundred","Four Hundred","Five Hundred","Six Hundred","Seven Hundred","Eight Hundred","Nine Hundred"}),"")," ",IFERROR(IF(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{1,2,3,4,5,6,7,8,9,10},{11,1,2,3,4,5,6,7,8,9}),1)="1",LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{1,2,3,4,5,6,7,8,9,10},{1,2,3,4,5,6,7,8,9,10}),1),{"0","1","2","3","4","5","6","7","8","9"},{"Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"}),LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{1,2,3,4,5,6,7,8,9,10},{11,1,2,3,4,5,6,7,8,9}),1),{"0","1","2","3","4","5","6","7","8","9"},{"","","Twenty","Thirty","Forty","Fifty","Sixty","Seventy","Eighty","Ninety"})),"")," ",IFERROR(IF(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{1,2,3,4,5,6,7,8,9,10},{11,1,2,3,4,5,6,7,8,9}),1)="1",LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{1,2,3,4,5,6,7,8,9,10},{1,2,3,4,5,6,7,8,9,10}),1),{"0","1","2","3","4","5","6","7","8","9"},{"","","","","","","","","",""}),LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{1,2,3,4,5,6,7,8,9,10},{1,2,3,4,5,6,7,8,9,10}),1),{"0","1","2","3","4","5","6","7","8","9"},{"","One","Two","Three","Four","Five","Six","Seven","Eight","Nine"})),"")," ","Rupee"," ",IFERROR(IF(LEN(FIND(".",A12))>0,"And",""),"")," ",IFERROR(IF(MID(A12,FIND(".",A12)+1,2)="1","Ten Paisa",""),"")," ",IFERROR(IF(MID(A12,FIND(".",A12)+1,1)="1",LOOKUP(MID(A12,FIND(".",A12)+2,1),{"0","1","2","3","4","5","6","7","8","9"},{"Ten","Eleven Paisa","Twelve Paisa","Thirteen Paisa","Fourteen Paisa","Fifteen Paisa","Sixteen Paisa","Seventeen Paisa","Eighteen Paisa","Nineteen Paisa"}),LOOKUP(MID(A12,FIND(".",A12)+1,1),{"0","1","2","3","4","5","6","7","8","9"},{"","","Twenty","Thirty","Forty","Fifty","Sixty","Seventy","Eighty","Ninety"})),"")," ",IFERROR(IF(MID(A12,FIND(".",A12)+1,1)="1",LOOKUP(MID(A12,FIND(".",A12)+2,1),{"0","1","2","3","4","5","6","7","8","9"},{"","","","","","","","","",""}),LOOKUP(MID(A12,FIND(".",A12)+2,1),{"0","1","2","3","4","5","6","7","8","9"},{"","One Paisa","Two Paisa","Three Paisa","Four Paisa","Five Paisa","Six Paisa","Seven Paisa","Eight Paisa","Nine Paisa"})),"")," ","Only."))
Re: Indian Currency Number to Word
some times macro causes harm to your pc. try excel.
I am trying to highlight the running row and column every time i working or changing the cell
Re: Vlookup formula
attach sample file.
Re: Formula for counting if two criteria are meet
use sumifs its very easy.
vlookup multiple values and return multiple values in one cell with comma seperated
Quote from royUK;658274You don't spend much time explaining your problem!!
You can only have one formula result per cell and lookups would alway return the same answer.
But i am badly have to solve this problem.anyway thanks.