Search results

Hi, Maybe you are looking for something like this. http://chandoo.org/forums/topic/help-for-small-formula-index-and-match Jai

Hi, Try =IF(LEFT(A1,2)=08,REPLACE(A1,1,2,""),A1) Jai

Hi Dhamo, Unfortunately you can't undo changes made after running a macro. Jai

You are welcome Mohan !! Jai

Hi, Try, =IFERROR(INDEX(Sheet9!$A$1:$E$7,MATCH(Sheet8!A2,Sheet9!$A$1:$A$7,0),MATCH(Sheet8!B2,Sheet9!$A$1:$E$1,0)),0) You have to replace Sheet8 with Sheet1 and Sheet9 with Sheet2. Jai

Hi, Try, =IF(C7="","111 Winner",IF(C7="L",C6,IF(C7="W",C7))) Jai

Hi, Your first 3 sheets in Sheet1, Sheet2, Sheet3 and result in Sheet4. With the data starting in cell A1 of all sheets, =SUMIFS(Sheet1!$B$2:$B$8,Sheet1!$E$2:$E$8,Sheet4!A2,Sheet1!$F$2:$F$8,1) +SUMIFS(Sheet2!$B$2:$B$4,Sheet2!$E$2:$E$4,Sheet4!A2,Sheet2!$F$2:$F$4,1)...

I'm sorry. Not sure why the code aligns to left after posting.

Hi Abbas, DO NOT TRY THIS CODE ON YOUR SHEET. I am still learning VBA and came up with this. This gives the required result to an extent but looks like I have to click at leat 4-5 times on YES or NO button to get rid of the message box. Also, even when I select YES, it doennt seem to save...

Hi, Shouldn't it be myval instead of myisn? Try, = Application.WorksheetFunction.VLookup(myval, Range("N:O"), 2, 0) Jai

Hi, I have the combo box in D13 referenced to cell A12. The input range for combo box is in A13:A17 and the actual data in B13:C20. Try, =INDEX(B13:C20,MATCH(OFFSET(A13,A12-1,0),B13:B20,0),2) You have to change range references to suit your needs. Jai

Hi, Most likely reason is H7 and H8 do not hold the same value. H8 might have an extra space or a character. Try H7=H8 in any other cell and see the result. If they are same then result would be TRUE else FALSE. Jai

Hi Joe, You are welcome!!! I am sure someone will come along and take care of the second question. Jai

Hi, Try this. =SUBTOTAL(9,OFFSET(A1,1,0):OFFSET(A1,10,0)) This would add A2:A11. Not sure about second question though. Jai

Hi, Not sure how you are getting the counts as you mentioned in the post. When I tried, I got the below counts. No. 1 2 3 4 5 6 7 Request:45666 208956 201212 206878 207595 204568 203225 Date: 1-Dec 1-Dec 1-Dec 2-Dec 2-Dec 2-Dec 2-Dec Silver: 1 3 3 3 5 0 Gold: 0 0 1 0 0 1 1-Dec-12...

Hi, I cant download your file due to work restrictions. I tried with some fictitious data in F11:F20. Try, =(SUM(F11:F20)-MAX(F11:F20)-MIN(F11:F20))/8 Jai

Hi Faseeh, Thanks a lot for explaining this. Jai

Hi Faseeh, You mean to say since the grades are texts, they cant be added using sumproduct and I am getting #Value! error. So sumproduct works only for numbers. Right? Jai

Hi, =SUMPRODUCT((A2:A35=A21)*(B1:G1=E1)*(B2:G35)) gives correct answer for the number of marks. =SUMPRODUCT((A2:A35=A21)*(B1:H1=E1)*(B2:H35)) gives an error. Jai

Hi All, A small sumproduct question. Can sumproduct be used to look up text data? I have a fictitious data with names in column A, subject 1, subject 2 etc in column B onwards and Grades in column H.( Grades are A, B etc.) Using sumproduct I can find the marks obtained by a person in any...

Hi Tazz, I think you would need a macro to do it. I am not good with macros. Maybe someone else can help you out here. Jai

Hi Tazz, Looks like you do not have a header row. So you are getting this message. Insert a header row and try again. Later you can delete the header row. Jai

Hi Tazz, Assuming the data in in range A1:A350. Select the range and click on Data - > Advanced filter. Select Copy to another location and enter B1 in copy to field. Leave criteria range blank. Select Uniue records only. This should give you the unique records in the list. Jai

Hi Tazz, Try advanced filters. Select unique records only. Jai

Hi, You can use the function Rank. Jai