Search results

Do you have a column where you are storing the status....and a column where the closed date is stored. You can then have a nested if to check the status and closure date and then count the days. By default if the issue has not been closed you would use today's date to calculate the number of...

OleMiss, Try the below code... Sub autoFitMergeCells() Dim strInput As String strInput = InputBox("Enter data") [dataCell] = strInput With Range("dataCell") .WrapText = True .RowHeight = .RowHeight * 2 End With End Sub ~VijaySharma

Kanu, Welcome to Chandoo.Org forums.. Have you tried using Conditional Formatting to acheive this. If not, please give it a try. HTH ~VijaySharma

Hi Karen, Do you have the flexibility to use another Helper column.. and use the Datediff function (hidden one) =DATEDIF(Date1, Date2, Interval) Where: Date1 is the first date, Date2 is the second date, Interval is the interval type to return. Set interval as d for days... more...

Karenlb, Welcome to Chandoo.Org forums... are you saying this... =SUMPRODUCT(--(issuestatus="closed"),--(actionopendate<=C5)) =SUMPRODUCT(--(issuestatus="pending"),--(actionopendate<=C5)) =SUMPRODUCT(--(issuestatus="approved"),--(actionopendate<=C5)) And use this data to...

Aksoni, Welcome to Chandoo.Org Forums... You can get this done by checking the International Setting and then apply the desired format through VBA). In your code you can check what is the current system default setting and change accordingly. Dim lngDateFormatType as long...

Basavaraj, You can have Calculated Fields inside your Pivot table... HTH ~VijaySharma

Sonia, Yes there is always another way out... Sheets(array("sheet1","sheet2","sheet3")).Copy The above line will automatically create a new workbook for you with the sheets copied from the master... (you need to change the names as per the actual ones). You will now have 2 workbooks...

Aratrika, Try this... on the Payslip sheet (assuming the Employee ID is in cell A1) =vlookup(A1,OtherSheetForDOB!A1:B500,2,0) Assuming the OtherSheetForDOB is the name of the sheet, and column A contains Employee ID and Columns B contains Birth Date). If using Excel 2003 ... below is...

It is the position of the elements of the array which will be summed up... you can try using SUM (SUMIF(range, criteria sum_range) + SUMIF(range, criteria sum_range)) In your sample data, if you position 1 at the same cells in Col A and B you will get the answer. So if Cell A2 and B2 have 1...

Jagadeesh, You need to add one helper row and column with your data. In cell B55 put =sum(B3:B54) and copy this till DV55 In cell DW3 put =sum(B3:DV3) and copy down till DV54 post this... you can use the below code to get the output... Sub ShowHide() 'hiding the rows...

Mazahrm, Read the below to understand why SUMIFS was not giving the result (copied from online help). Each cell in the sum_range argument is summed only if all of the corresponding criteria specified are true for that cell. For example, suppose that a formula contains two criteria_range...

Jagadeesh, In this you have 51 rows and 125 columns of data.... what are the chances of the entire column not having any data point at all (having only zero). and the same applies to the Rows as well... ~VijaySharma

Mazahrm, Try the below to get the results.... =SUM(IF(F7:F14=1,H7:H14),IF(G7:G14=1,H7:H14)) This is a array formula, press CTRL + SHIFT + Enter to get the output. In the meantime trying to figure out why SUMIF and Sumproduct is also not giving the results.... looks like a case of AND...

Sonia, Here is your above code... Name = ClientName (j) & ".xls" ActiveWorkbook.SaveAs "D:Files" & Name, FileFormat:=_ x1Excel18, Password:="",WriteRespassword:="", ReadOnlyRecommended: =False, CreateBackup:=False Workbooks.Open Filename: "D: Master file.xls" Try to use the below...

Rockstarr319, Welcome to Chandoo.Org Forums. Try the below VBA code 1. Open your excel file (please make a duplicate copy just in case). 2. Hit ALT + F11 to start the VB Editor 3. On the VB Editor window click on Insert menu and then click on Module 4. Copy and Paste the below code...

Raju, welcome to Chandoo.org Forums. Your requirment is not clear and is making dificult to understand what you exactly need. Please post your specific query and we may be able to help. ~VijaySharma

Aratrika, Assuming Income in Cell A2. =IF(A2<=180000,0,IF(A2<500000,(A2-180000)*10%,IF(A2<800000,32000+(A2-500000)*20%,92000+(A2-800000)*30%))) ~VijaySharma

Guity, I would recommend looking at the graphs that were submitted by lots of persons for the contests ran by Chandoo... they might give a fair idea... ~VijaySharma

That will depend on the extensibility exposed by the software, try adding the component references and see if the objects have been exposed. if yes then you may use the Object Browser (within the VB Editor) and look for the methods and properties that you may use and then write code to logon and...

Certainly not possible using vlookup only... however you can look at the code in the below post to get some help... http://chandoo.org/forums/topic/check-4-duplicate-copy-format-values-of-orginals-row-over-dupicates-row ~VijaySharma

Hi Tia, I have got your file and working on the same. Will revert once completed. ~VijaySharma

Tia, Is it possible for you to send over your file with sample data over so that the code can be adjusted to work on the real file. sharma.vijay1 @ gmail.com Regarding learning VBA... you can always enroll yourself on the VBA School at Chandoo. ~VijaySharma

Tia, The below code will take care of the duplicates (repeats) in the new list as well... Thanks for a very good question... Option Explicit Option Compare Text Public OriginalCell As String Public currentFoundCell As String 'will store the address here Sub checkForDuplicates()...

PS: This code has assumed that there are no repeats in the new list at all... If there are repeats we would need to modify the code.. ~VijaySharma