Posts by Faysalalam2008

Faysalalam2008 · Oct 30th 2013

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"

Faysalalam2008 · Oct 29th 2013

Re: SumIf cell is equal to any in a list

Quote from Brewface;689274

Hello 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 example

Name Hours
Doe 2
Smith 3
Murphy 3
Roberts 8
Mollard 4

Names belonging to Division A_

Doe
Mollard

Division 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

Display More

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

Faysalalam2008 · Oct 28th 2013

Re: VLOOKUP multiple arguments

=INDEX($C$2:$C$7,MATCH(C11&D11,$A$2:$A$7&$B$2:$B$7,0))
[TABLE="width: 123"]

[tr]

[TD="class: xl65, width: 64, align: right"]CNT+Shift+Enter
[/TD]

[/tr]

[/TABLE]

Faysalalam2008 · Oct 28th 2013

Re: Conditional formatting via formula

Select your Range
Go to Conditional Formula-Custom and type
=FIND("-",A1,1)
or
=FIND("-",A1)
That will work

Faysalalam2008 · Oct 28th 2013

Re: validations for negative values

Select Cell or Whole column,
Go to data Validation select Custom and type this
=$I1>=0
Click Ok

Faysalalam2008 · Oct 27th 2013

[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]

111

[/td]

[td]

450

[/td]

[/tr]

[tr]

[td]

2

[/td]

[td]

222

[/td]

[td]

200000

[/td]

[td][/td]

[td]

222

[/td]

[td]

500

[/td]

[/tr]

[tr]

[td]

3

[/td]

[td]

333

[/td]

[td]

300000

[/td]

[td][/td]

[td]

333

[/td]

[td]

640

[/td]

[/tr]

[tr]

[td]

4

[/td]

[td]

444

[/td]

[td]

400000

[/td]

[td][/td]

[td]

111

[/td]

[td]

687

[/td]

[/tr]

[tr]

[td]

5

[/td]

[td]

555

[/td]

[td]

500000

[/td]

[td][/td]

[td]

444

[/td]

[td]

5690

[/td]

[/tr]

[tr]

[td]

6

[/td]

[td]

666

[/td]

[td]

600000

[/td]

[td][/td]

[td]

555

[/td]

[td]

4569

[/td]

[/tr]

[tr]

[td]

7

[/td]

[td]

777

[/td]

[td]

700000

[/td]

[td][/td]

[td]

111

[/td]

[td]

7890

[/td]

[/tr]

[tr]

[td]

8

[/td]

[td]

888

[/td]

[td]

800000

[/td]

[td][/td]

[td]

999

[/td]

[td]

26460

[/td]

[/tr]

[tr]

[td]

9

[/td]

[td]

999

[/td]

[td]

900000

[/td]

[td][/td]

[td]

777

[/td]

[td]

5426

[/td]

[/tr]

[tr]

[td]

10

[/td]

[td][/td]

[td]

888

[/td]

[td]

2564

[/td]

[/tr]

[tr]

[td]

11

[/td]

[td][/td]

[td]

111

[/td]

[td]

56987

[/td]

[/tr]

[tr]

[td]

12

[/td]

[td][/td]

[td]

111

[/td]

[td]

560

[/td]

[/tr]

[tr]

[td]

13

[/td]

[td][/td]

[td]

222

[/td]

[td]

400

[/td]

[/tr]

[tr]

[td]

14

[/td]

[td][/td]

[td]

111

[/td]

[td]

6980

[/td]

[/tr]

[tr]

[td]

15

[/td]

[td][/td]

[td]

555

[/td]

[td]

578

[/td]

[/tr]

[tr]

[td]

16

[/td]

[td][/td]

[td]

111

[/td]

[td]

300

[/td]

[/tr]

[tr]

[td]

17

[/td]

[td][/td]

[/tr]

[tr]

[td]

18

[/td]

[td][/td]

[/tr]

[tr]

[td]

19

[/td]

[td][/td]

[/tr]

[/TABLE]

[TABLE="width: 70"]

[tr]

[td]

Codition 1=
[TABLE="width: 233"]

[tr]

[TD="class: xl65, width: 233, colspan: 3"]SUMPRODUCT((G:G=G2)*(H:H))<B2

[/tr][/td][tr]

[/tr]

[/TABLE]
[/TD]

[/tr]

[tr]

[td]

Codition 2=
[TABLE="width: 479"]

[tr]

[TD="class: xl66, width: 479, colspan: 6"]SUMPRODUCT((G:G=G2)*(H:H))<INDEX($A$2:$E$10,MATCH(G2,G:G,0),2)

[/tr][/td][tr]

[/tr]

[/TABLE]
[/TD]

[/tr]

[tr]

[td]

Codition 3=
[TABLE="width: 479"]

[tr]

[TD="class: xl66, width: 479, colspan: 6"]SUMPRODUCT((G:G=G2)*(H:H))<VLOOKUP(G2,A:E,2,FALSE)

[/tr][/td][tr]

[/tr]

[/TABLE]
[/TD]

[/tr]

[tr]

[td]

Codition 4=
[TABLE="width: 479"]

[tr]

[TD="class: xl66, width: 479, colspan: 6"]SUMPRODUCT((G:G=G2)*(H:H))<150000

[/tr][/td][tr]

[/tr]

[/TABLE]
[/TD]

[/tr]

[/TABLE]

[TABLE="width: 896"]

[tr]

[TD="colspan: 4"]I m trying to validate data in Column H.
[/TD]

[td][/td]

[/tr]

[tr]

[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]

[/tr]

[tr]

[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]

[/tr]

[/TABLE]
[/TD]

[td][/td]

[/tr]

[/TABLE]

Faysalalam2008 · Oct 27th 2013

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.

Faysalalam2008 · Oct 27th 2013

[TABLE="width: 896"]

[tr]

[TD="colspan: 4"]I m trying to validate data in Column H.
[/TD]

[td][/td]

[/tr]

[tr]

[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]

[/tr]

[tr]

[TD="colspan: 3"]reference code 111[/TD]

[td][/td]

[/tr]

[/TABLE]

[TABLE="width: 64"]

[tr]

[/tr]

[tr]

[/tr]

[tr]

[td][/td]

[/tr]

[/TABLE]

Faysalalam2008 · Aug 28th 2013

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".

Faysalalam2008 · Aug 27th 2013

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.

Faysalalam2008 · Aug 27th 2013

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).

Faysalalam2008 · Aug 27th 2013

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."))

Faysalalam2008 · Aug 15th 2013

Re: Indian Currency Number to Word

some times macro causes harm to your pc. try excel.

Faysalalam2008 · Jul 5th 2013

Re: VLookup multiple columns and return to a single cell seperated by commas

well very good but what should do if i lookup this from 1st sheet and result in 2nd sheet.

thanks

Faysalalam2008 · Apr 11th 2013

I am trying to highlight the running row and column every time i working or changing the cell

Faysalalam2008 · Apr 9th 2013

Re: Vlookup formula

attach sample file.

Faysalalam2008 · Apr 7th 2013

Re: Formula for counting if two criteria are meet

use sumifs its very easy.

Faysalalam2008 · Apr 7th 2013

Re: vlookup multiple values and return multiple values in one cell with comma sepera

Please watch this time.

Faysalalam2008 · Apr 6th 2013

vlookup multiple values and return multiple values in one cell with comma seperated

Quote from royUK;658274

You 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.

Faysalalam2008 · Apr 6th 2013

Re: return multiple vlookup and return multiple values in one cell

find out what we can do?