Search results

Hi Pegaso, Pls share the solution you have when name of files are known. We can try to tweak the same to your requirements, rather than recreating some scratch. Regards, Prasad DN

Hi, Can you post some real data ... I was thinking of alternate formula to combine last 4 columns where you have data.. Regards, Prasad DN

Hi, I guess the earlier formula without 1+ worked perfectly when I was testing and after including 1+ it was showing incorrect results! Am I missing anything? I tried with B1 cell value AA and B2 cell value B, expected result is A, but surprisingly it is giving C. I also changed the...

Hi, Yes, DATES in the formula are names and not functions. Is it possible to upload the file? could be something to do with format of dates. regards, Prasad DN

Hi, I just opened your file, can you clarify few points: 1. you have multiple entries with same booking number in sheet1. does in sheet 2 also you get multiple entries or single? 2. What about those records in Sheet1 without booking number. Regards, Prasad dN

Hi BCObrien, Use this formula in B28: =SUMPRODUCT(($A$2:$A$22)*($B$2:$B$22=$A28)*(B$25>=$C$2:$C$22)*(B$25<=$D$2:$D$22)) You can drag to right and down to see results of all the dates and employees. One catch: You should not have blank cells in end date column like position 19 and 20...

Hi Hui, I believe the OP wants formula to get the position values in payperiod table (rows A28 and down). Regards, Prasad DN

Hi, Pls use Vlookup formula. regards, Prasad DN PS: For more details on vlookup(), search at top right box.

Hi, I was not able to recall that small() is the opposite of large(), in the attached file of mine, replace in formula, Large to small. You will get the result in ascending order ;) Regards, Prasad DN

Hi, Pls see if file works, the results are in descending order. :( Regards, Prasad DN

Hi Pls help me understand why you have formula referring to $I$4 and also when I apply the formula with 500 in stock, it shows 150 days cover, while it should be 144! Regards, Prasad DN Edit: $I$4 to $I$2 resolves the issue.

Hi if the stock is less than first month demand, it shows N/A instead of value. Like if stock is 50, it should show 15 days, for the given situation. Regards, Prasad DN

Hi, Assuming you have 30 days in a month, i have made use of helper rows to achieve your requirements. See if it helps. The formula are very long.. and sure you can work it out to make short. And i have also used different formula for first column and for rest of columns same in two helper rows...

Sorry, I did not understand you. :( Are you looking to show graph with date range July 2010 to June 2011, and monthwise? Regards, Prasad DN

Hi, You could do this in many ways. here I have mentioned three methods: 1. enter the cut off date (04/01/2015) in lets say F1 cell and then use the below formula and drag down to your required rows: =IF(C2<$F$1,4,6) 2. using datevalue(): =IF(C2<DATEVALUE("04/01/2015"),4,6) 3. using date's...

:)

Hi, Alternate solution: A) Sort the data with: 1. level 1 PDD_num (A-Z) and 2. level 2 Insert date column (Z-A), 3. on the dialog box, select treat anything looks like number as number. This (3rd step is required in this case as the data and time is stored as general format.) B) Apply a...

Hi, Kindly upload sample file with sample raw data. And manually put expected output. regards, Prasad

hi, See if this works. Regards, Prasad DN

Hi, You can store the lookup value in one variable (to avoid multiple times lookup function) and check if its a date, if yes apply the formatting and if not show as it is. see below code: If VBA.IsDate(Range("A1").Value) Then TextBox1.Value = VBA.Format(Range("A1").Value...

Oops! some changes in formula is required: Pls use: =IF(IFERROR(IF(IFERROR(VLOOKUP(LEFT(B9,1),_S1,1,0),"")=LEFT(B9,1),VLOOKUP(MID(B9,2,LEN(B9))/1,_p1,1,0),IF(IFERROR(VLOOKUP(LEFT(B9,1),_s2,1,0),"")=LEFT(B9,1),VLOOKUP(MID(B9,2,LEN(B9))/1,_p2,1,0),NA())),"")="","",B9)

Hi bilboy, I did find something thats working, it looks very ugly formula though :( =IF(IFERROR(IF(VLOOKUP(LEFT(B9,1),_S1,1,0)=LEFT(B9,1),VLOOKUP(MID(B9,2,LEN(B9))/1,_p1,1,0),IF(VLOOKUP(LEFT(B9,1),_s2,1,0)=LEFT(B9,1),VLOOKUP(MID(B9,2,LEN(B9))/1,_p2,1,0),NA())),"")="","",B9) Take a look at...

@Deepak and @Nebu, The suggestion would make the PT not to show Field Headers, i.e. Category for row and drop down of columns. I hope thats what OP wanted. @OP, If you are looking only to remove the column heading dropdown and to retain Category label then.. Just hide the entire row...

Hi, At the original post, if the question is a alternate function for numbervalue()? then instead of: IFERROR(_xlfn.NUMBERVALUE(RIGHT(M2,3)),_xlfn.NUMBERVALUE(RIGHT(M2,2))) The revised formula will be =IF(ISNUMBER(RIGHT(M9,3)/1),RIGHT(M9,3),RIGHT(M9,2)) And for example ...

Hi, In your file, with existing formulas in B5, add the below formula in B6 and then drag down. =B5&","&A6 The last row will have all in one cell. Regards, Prasad DN