Search results

Hi Ananthram Your query is not clear to me. Could you please share the spreadsheet. Amritansh

Hi Ananthram You can use the following formula, =IF(AND(A1="asd",B1="zxc"),CONCATENATE(A1,B1),A1) Amritansh

Hi Joeman You can use the following formula: =(B2-A2)*24-(C2/60) Start End Lunch Time 8:30 AM 5:30 PM 30 min 8.5 Amritansh

Hi You can also try this: =MID(A1,SEARCH("_",A1,SEARCH("_",A1)+1)+1,(SEARCH("_",A1,SEARCH("_",A1,SEARCH("_",A1)+1)+1))-((SEARCH("_",A1,SEARCH("_",A1)+1)+1))) Amritansh

Hi Nitin Please post the sample data sheet. Amritansh

Hi Ksandra You can use the conditional formatting based on formula. Select the total column, and put the following formula in Conditional Formatting =B1=C1 Amritansh

Hi Hoomantt I don't know if I have understood your problem correctly. But if you want to rename the sheet based on the cell value, you can use the following macro. Press Alt F11 and paste the following code as follows. In the workbook, Private Sub Workbook_Open() Call NameSheet End Sub...

Hi Tony Instead of using nested If, you can make a table for the commision and use vlookup. Amritansh

Hi Harry I think I am wrong but I understand that you have the branch name and the corresponding branch code in a table. So you can use VLOOKUP function to fetch the branch code from the table. Amritansh

Hi Nazmul Try this by seting A1 as 0. =A1+1&"-"&A2 Amritansh

Hi Nazmul Welcome to the forum. Your question is not very clear. I think you want to achieve the following results: A B 5 1-5 7 1-7 For this you can use the formula = "1-"&A1

Hi I have put From Date in B1 and To Date in B2. The data validation is applied in D1 as follows, =AND(D1>=$B$1,D1<=$B$2) Amritansh

I think you can directly use the following formula for date calculation =today()+ (if(A1="Sea",6,4)*7) Amritansh

See if this helps: =DATEDIF(A1,B1,"md") For variations of datedif, you can explore, http://office.microsoft.com/en-us/help/datedif-function-HA001160981.aspx Amritansh

Hi fjo2818 You don't need to have all the values as with the MATCH function you can search for value less than the particular value. So you will only have to make a table with boundary conditions i.e. 0, 1501, 2501, 3501, 4501, 550 and 6501. Then you can use the formula mentioned earlier...

Hi fjo2818 Welcome to the forum. As mentioned by NARAYANK991, you can form the table as follows for the slabs. Weight (Col A), Tariff (Col B) 0, 19 1501, 38 2501, 76 Then the following formula can be used to look up the value. =INDEX($B$1:$B$3,MATCH(A8,$A$1:$A$3,1)) Amritansh

Hi Josdev Please try this. =IF((B1-MIN(IF((B1-($B$1:$B$9))*($A$1:$A$9=A1)*($C$1:$C$9=C1)>0,(B1-($B$1:$B$9))*($A$1:$A$9=A1)*($C$1:$C$9=C1))))=0,B1,(B1-MIN(IF((B1-($B$1:$B$9))*($A$1:$A$9=A1)*($C$1:$C$9=C1)>0,(B1-($B$1:$B$9))*($A$1:$A$9=A1)*($C$1:$C$9=C1))))) Amritansh

Hi I am new to macros. I have written the following code to prevent anyone from copy/pasting the values to validated cell which is activated whenever the selection changes in the worksheet. Sub DisablePaste() Dim X As Variant On Error Resume Next X = ActiveCell.Validation.Type If Not...

Hi Shabzo The requirements mentioned by you are not clear. Could you please share the sample data so we can help you. Amritansh

Hi Josdev As you can see, in the data used by me I have used no particular order for dates. So it will work with or without any order. Regarding your second point that some rows may not have data, could you please clarify how do you want to treat those rows. Cheers Amritansh

Hi Josdev I tried with the date format "dd/mm/yyyy" and faced the same issue. This may be due to the reason that MS Excel is not supporting this particular date format but I am not sure. Experts may help on this. But as a workaround you can enter the dates as mm/dd/yyyy and later change the...

Hi Amit You can slightly modify the formula mentioned by Chandoo as follows: =DATE(YEAR(min(A5:A300)),CHOOSE(MONTH(min(A5:A300)),1,1,1,4,4,4,7,7,7,10,10,10),1) Cheers, Amritansh

Hi Getco In case the values may repeat in the array, the following formula can be used. =LARGE(A1:A9,COUNTIF(A1:A9,MAX(A1:A9))+1) Cheers, Amritansh

Hi Josedv I have slightly changed the data to include all the possibilities. John Lewis 13-Oct-12 Madrid 23-Sep-12 John Lewis 13-Oct-12 Paris 13-Oct-12 John Lewis 13-Oct-12 New York 13-Oct-12 Mike Smith 13-Oct-12 Madrid 21-Sep-12 Mike Smith 13-Oct-12 Paris 13-Oct-12 Mike Smith...

Hi aks_npti See if this helps. The data has been arranged in the range A1:E10. For unique list of cities in column F: =IF(COUNTIF(A2:A10,A2)=1,A2,"") For sum for cities across months: =SUM(IFERROR(SEARCH(F4,$A$2:$A$10),0)*($B$2:$E$10)). Press Ctrl+Shift+Enter to use array formula for...