Search results

@Kalpesh, Use the below code to get the output Sub CopyData() Dim strBillNo As String Dim dtDate As Date Dim strDescription As String Dim lngValue As Long Sheets("Sheet1").Select strBillNo = Range("B5").Value dtDate = Range("E7").Value strDescription = Range("C12").Value...

FAhmed, Welcome to Chandoo.Org Forums... Have a look at the below link http://blog.contextures.com/archives/2011/06/10/delete-excel-drilldown-sheets-automatically/ ~VijaySharma

Parminder, Glad that I was able to help you out. sharma.vijay1@gmail.com +91-9811996454 ~VijaySharma

Gautam, Welcome to Chandoo.Org Forums. I believe you can accomplish this using a simple VLOOKUP formula. Cell A1 Contains the drop down... in Cell C1 put the below formula =IFERROR(VLOOKUP(A1,F:G,2,0),"Word Not Found") This will search for the word from A1 in Column F and if a match...

Parminder, Assuming Cylinder Given is Col A and Cylinder Receiving is Col B A-------B-------C 10-----20------- 20-----40------- 30 40 Put the below formula in C2 =IF(SUMPRODUCT(($A$2:$A$5=B2)*B2)=0,($A$2:$A$5),"") It will show a blank if Cylinder is received... It will show the...

bri, Welcome to Chandoo.Org forums. Yes, you may do this in multiple ways... The easiest one would be (excel 2007) 1. Go to the Data Tab (on ribbon) 2. Click on From Access 3. Link to the database and the Table which you want people to view (in your case this would be a query to take...

Arnold, There is already a site which you may use.. http://www.ms-iq.com (upto 2003 version) 2007 and above http://www.skills-assessment.net/ Do give this a look.. ~VijaySharma

Smc, I need time till this weekend to send over the solution to you. ~VijaySharma

Hi Guity, If possible send me the template to look at, I will then add a few examples of how and where to use the ActiveX control and revert. ~VijaySharma

SirBJ, One thing I would recommend immediately is to save your file as Excel Binary File (xlsb). This will reduce the size of the file (to a huge extent) and working on the file will be even easier. Regarding the UDF, as Hui asked, please post this for review. ~VijaySharma

Hi Guity, This is not possible for the in-cell Drop down validations... However you may use the ActiveX controls to accomplish the same. ~VijaySharma

Not sure what is the data behind the formula, however if you point to one cell only as below, it will give you the count of times that value exists in A1:A19. =SUMPRODUCT(--(A1:A19=G1)) G1:G3 will not work as the array elements are not matching ~VijaySharma

A1--------------------------- B1----------C1 Project Start Date ------ Due Date -------First Submission Date 12-Jan-2011 ------------- 13-Jan-201 -----14-Jan-2011 Formula in Cell D2 : =B2-A2 formatted as Number will give you 2 Formula in Cell E2 : =C2-B2 formatted as Number will give you 1...

You would need to use the FileScripting Object to first search for the existance of the PPT file, if found then use the code above to open the presentation. ~VijaySharma

Premakumar, How is your data stored.. as this is simple date-date calculation and will give you the number of days as a difference. ~VijaySharma

Ashish, This is not possible without a macro... And this has been a recent article on DailyDoseOfExcel... see the link below and use the last code in the comments area of the article. http://www.dailydoseofexcel.com/archives/2011/11/30/udf-for-cumulative-sum/ ~VijaySharma

This will work for the entire column A if there are no blank cells in between. So all the cells you have suggested above will be hidden when you run the code. You are free to change the start Column range on the code above... this code works on single column and multiple rows... ~VijaySharma

Its because you did not want to hard code the dates, now you simply have to change the dates in the cells C2 and C3 for anothere date range. ~VijaySharma

Guity, Error Handler are functions that will allow us to write a formula and also put an On Error Do This clause. =IFERROR(1/0,"There was Division by Zero, sorry cannot do this") Is an example of Error Handler... In previous versions of Excel, ISERROR was used to tackle these. To get...

Hi Sri, Welcome to Chandoo.Org Forums. Use the inbuilt DEC2BIN function for this. ~VijaySharma

Guity, Did you try the steps on my previous post... this will help you to lock the sheet and prevent the user from changing anything. There are 3 items mixed here and causing confusion. 1. How to Protect my Worksheet 2. How to protect my VBA Code 3. How to make my sheet very hidden, so...

Guity, If you need to lock the scroll area of your worksheet, put the below code on any Module and run the code by pressing F5, after that the Scroll Area for that sheet will remain fixed... You will even not be able to click on any other cells which are outside the range you define. Sub...

smc, I have checked your files and this is not so straight forward as it seems. We would need to write VBA code to accomplish your base objective of auto expansion of rows on the reporting sheet. I will try to do this over the weekend, and will let you know once done. ~VijaySharma

Hi Carica, Below is the code Sub HideRows() Sheets("Sheet1").Select Range("A1").Select Do While ActiveCell.Value <> "" If ActiveCell.Value = 1 Then ActiveCell.EntireRow.Hidden = True ActiveCell.Offset(1, 0).Select Else ActiveCell.Offset(1, 0).Select End If Loop...

Hi Veronica, try the code below Selection.AutoFilter ActiveSheet.Range("$A$1:$B$18").AutoFilter Field:=2, Criteria1:="=>180", _ Operator:=xlAnd Please make changes to cell referrencing as per your Columns ~VijaySharma