Search results

Hi Try this code. I guess you have only questionnaire files in the folder, if not remove all other files from the folder. Copy paste this code in the Master File and run. Adjust the folder path in the code. Sub kTest() Dim FName As String Dim Fldr As String Dim i As Long Dim ka...

You could also try this http://www.excelfox.com/forum/f11/remove-unused-custom-styles-vba-616/?highlight=custom which deletes only unused custom styles. Kris

@ Haseeb nice one.

Hi B2(Helper column), =SEARCH("spl",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"SPECIAL","spl"),"EXPRESS","SPL"),"EXP","SPL")," SP ","SPL"),"RATH","SPL")) C2, =LEFT(A2,FIND(" ",A2)-1) D2, =MID(A2,LEN(C2)+2,FIND(" ",A2,B2)-LEN(C2)-1) E2...

Hi I think this should also work, =IFERROR(MID(B3,LOOKUP(9.999E+307,SEARCH($G$3:$G$7,B3),$H$3:$H$7)+1,255),B3) Kris

Hi You may try this =IF(MIN(xyz)=0,SMALL(xyz,2),MIN(xyz))

Hi Try LARGE function.

=SUMPRODUCT(--(ISNUMBER(MATCH(Q3:Q170;P182:P184;0)));S3:S170;T3:T170) where P182:P184 holds the sizes 25,22 and 16 Kris

Guys, beware of volatile functions. http://www.decisionmodels.com/calcsecretsi.htm

Hi I assume A60:F60 hold column headers. In H61 =COUNTA(A61:F61)>0 Now activate Summary Sheet > Goto Data > Advanced Filter > Check 'Copy to another location' > List range: Select the actual range Sheet1!A60:F129 > Criteria Range: Sheet1!H60:H61 > Copy to A1...

Rather than filling the cells why don't you use abbreviation for different leave types ? Like SL for sick leave PL for Personal leave etc. and later you can easliy sum the leaves using countif formula. The drawback of these UDF is you have to calculate(F9) the workbook to get your total...

another one.. http://www.excelfox.com/forum/f13/excel-dynamic-waterfall-chart-317/

Hi Vijay, Try this VBA. Sub kTest() Dim ka, k, i As Long, d As Object, Sht As Worksheet With Worksheets("Main Data") ka = .Range("j11:r" & .Range("j" & .Rows.Count).End(xlUp).Row).Value2 End With Set d = CreateObject("scripting.dictionary") d.comparemode = 1...

Hi Try this array formula. =INDEX(A2:A4,MAX(IF(B2:C4=E3,ROW(A2:A4)-ROW(A2)+1))) where E3 holds item1 Kris

Hi try =INDEX($B$2:$E$4,MATCH(H2,$A$2:$A$4,0),MATCH(I2,$B$1:$E$1,0)) where H2 holds Customer I2 holds Quarter B2:E4 your data range. Kris

Another one.. =LOOKUP(M45,{"Andrew","987654612";"Peter","397846124";"Travis","039123456"}) Names must be in ascending order or =INDEX({"039123456","987654612","397846124"},MATCH(M45,{"Travis","Andrew","Peter"},0)) Kris

Hi If you are looking for a dependent data validation, have a look at here: http://www.excelfox.com/forum/f12/dependent-data-validation-using-only-formulas-111/ Kris

Hi, put 10 in D1 In D2 and copied down, =ROWS($D$2:D2)*$D$1-$D$1+1 & " - " &ROWS($D$2:D2)*$D$1 In E2 and copied down, =SUMPRODUCT(--($A$2:$A$100>=--LEFT(D2,FIND("-",D2)-2)),--($A$2:$A$100<=--MID(D2,FIND("-",D2)+1,5)),$B$2:$B$100) Kris adjust the range

For more Macro functions : http://www.excelfox.com/forum/f13/excel-macro-functions-get-cell-75/ Kris

Hi If your data in A2 on Sheet1 Select A2, Hit Ctrl + F3 > New > Name: CELLCOLOR Refers to: =GET.CELL(63,Sheet1!$A2) OK. Now in B2 =CELLCOLOR would give you the color index of A2 Kris

@ Muneer, Have you tested the formula ? If so what you get ? Kris

Try this array formula Assume your data in A2:A26, In B2 and copied down, =INDEX($A$2:$A$26,SMALL(IF(FREQUENCY(MATCH($A$2:$A$26,$A$2:$A$26,0),ROW($A$2:$A$26)-ROW($A$2)+1)>1,ROW($A$2:$A$26)-ROW($A$2)+1),ROWS(B$2:B2))) Kris

Hi You should better to use With Worksheets("Payments") count_val = .Range("n" & .Rows.Count).End(3).Row End With Also this article might help you to understand Usedrange in detail. http://www.excelfox.com/forum/f22/replacement-flawed-usedrange-property-374/ Kris

Ignore the modified version. The SEARCH function won't work here. Kris

You can copy down the formula. I made the Column absolute and the row relative. Evaluate is the part Excel4macro functions. So you can't use it directly in worksheet. Edit: this formula can be copy down as well. =IF(ISNUMBER(SEARCH($B$1,Nums)),$B$1,MIN(IF(Nums>$B$1,Nums))) Again...