Search results

Jagdeesh, Just to simplify Hui's example... =SUMPRODUCT((C:C>=100010)*(C:C<=100081)*(D:D)) Column C contains all the numbers and Colum D contains the Dollar values. However as per your original query, of summing automatically are you going to use a helper sheet like below...

Jagadeesh, When you say Pick the value do you mean any one of the 12 (as per the example above)... Or Do you want those 12 values in a drop down ~Vijay

Brad, On your master sheet define the named ranges for the items that you want to include in the body of the mail. Once the Save button is hit and the workbook has been saved you can extract the file name to be put in the subject or make the subject dynamically using the Requisition number...

This is not possbile directly... however you can use VBA and a USERFROM to accomplish.. Create a New Workbook Go to Visual Basis Editor Insert a Userform Insert a Label Control, give the name as lblMsg Once done... Double click on the form to go to Code view and delete everything...

Very much possible... I would recommend to keep the audit trail at the record level rather than Field level as per the code above. After your excel userform is populated with the record from the access table. You can declare a variable blnRecordDirty as Boolean. Store the old values in a...

Rodrigo, Have you tried the CHOOSE function... ~Vijay

Blank were considered as ZERO... if you evaluate your formula you will see that.. ~Vijay

Mike, Select you chart On the ribbon, on the Design Tab under Chart Tools Click on the Button Select Data In the Select Data Source dialog box, Click on Hidden and Empty Cells choose from Show Empty Cells as Gaps Zero HTH ~Vijay Sharma sharma.vijay1-at-gmail.com +91-9811996454

Indi, Try the code below (adjust the range as necessary) Sub foo() Dim myChkArr As Variant myChkArr = Sheet1.Range("A1:A27") myChkArr = Application.Transpose(myChkArr) Dim z As Variant testvalue = "ac " z = Filter(myChkArr, testvalue) If UBound(z) < 0 Then MsgBox...

Venkat, If VBA code is not an issue... do give a try to the code below... Sub ConvertToCells() Dim i As Integer Dim myRange() As Variant Dim myArr() As Variant, cntr As Long ReDim myRange(1 To Range("A3", Range("A65536").End(xlUp)).Count, 1) Dim strVal As String, RecComplete As...

Venkat, This can be easily done using VBA code; is that something that would help you. ~Vijay

Vijesh, By default all the cells are locked, and hence when we protect the sheet and then try to type something excel presents us with en error message. Below is what I have done in my 2007 sheet 1. Create a new workbook 2. Type http://www.microsoft.com in any cell 3. Protected the sheet...

Vijesh, I have tried this in 2007 and did not find any issues. I am not aware if this is a new feature in 2010. Vijay

Jag, Hui's formula is absolutely rocking... I have adjusted the same as per the sheet that you sent across. Use this formula in the Yello cells to get the output (however this is not 100% correct)...

Jag, =INDEX(FromWhichColumnYouWantData,  MATCH(WhatAreYourSearchingFor,  WhichColumnNeedsToBeSearched,  FALSE)) Try this simple explanation and let us know if you were able to get the output as desired. ~Vijay

Andrew, Can you please upload your file to Skydrive or something similiar. ~Vijay Sharma.vijay1-at-gmail.com +91-9811996454

Solution found for the Combox Box refresh as well In the sheet put any dummy formula such as MAX(E73); we know it will always have single value only. Then in the sheet code put the below rename the sheets as per the requirement Private Sub Worksheet_Calculate()...

Okay... here are the finding... 1. Tried using Data Validation... works OK 2. Tried using Data Validation with Dynamic Range assigned to the list.... work OK 3. Tried using Form Controls with Dynamic Range..... work OK 4. Tried using ActiveX control with Dynamic Range ..... NOT OK Ninad...

Houston We have a problem... The dynamic named range refreshes when assigned to a simple cell validation list using the Named range... However the (ActiveX) combobox list fill range does not refresh unless the workbook is saved ... closed and open again... Researching.... ~Vijay

Ninad, on the sheet 6a, I have create a new dynamic named range called as testList. =OFFSET(Scores,0,0,MATCH(E73,Scores,0),1) and then assigned this to the Drop Down list property. sending the file to you to have a look cheers ~Vijay

Ninad, Is it required for you to use the ActiveX controls only for this workbook? ~Vijay

No attachements.... I believe you cannot attach a file on this blog. or maybe Chandoo has not activated the feature. You can upload the file on a public share like Skydrive etc. and put the link here.. if you want please feel free to mail it to me at sharma.vijay1-at-gmail.com ~Vijay

Hui, After going through the workbook with the previous code; I was wondering why this way was utilized for the output desired... Somewhere the array V was getting duplicate values such as "bbe"... "ggone" which is why the result was not as expected. But then again; problem definition and...

Hui, The code is also getting rid of extra spaces which is cool. However the Message Box "Oh! Yeah" did not appear as the Expected Results sheet contains the extra spaces and the two cells do not compare.. Indi, I believe you don't want those extra spaces.... ~Vijay

Arshad, As you can already see, everyone is ready to help. All i think is missing is a good explanation of the real issue... if you can specify the extact requirement; it would make it a lot easier for everyone here to assist. Looking forward to your next post will max possible details on...