• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Search results

  1. N

    Using Range name for pivot table

    I have data from A1:K622 and I've created a dynamic range name "lstAuditMasterDetails" =OFFSET(AuditMaster!$A$1,0,0,COUNTA(AuditMaster!$A:$A),11). Row 1 has headers and data beginning from row 2. For the pivot table I am using this range name, but the field names the table picks for the data...
  2. N

    Determine max and min values for a range of cells

    See attached file for details. It's difficult to explain in simple words for me. Appreciate your help. https://skydrive.live.com/redir?resid=EF586DA077D21A56!7747 Regards, Ninad.
  3. N

    Populate cells based on condition of another range

    Hi, On Sheet1 I have a range A1:D10 (A1:D1 is the header row) and A2:D10 are values. Columns B and C take a value from 1-5, both inclusive. On Sheet2 is a range A1:E5. What I am looking to do is if Sheet1!B2 = 1 and Sheet!C2 = 3, then from the bottom left cell (i.e. A5) go 1 cell right (A5...
  4. N

    Function assistance

    I wish to write a Select Case statement in a Function, say, FormatCell(cell_reference) passing a cell value. How can this be done. Function FormatCell(ByVal Target as Integer) Select Case Target Case Is <=1000 Selection.NumberFormat = "0.00" . . . and so on..... TIA Regards...
  5. N

    Incrementally name a range of cells

    I saw this on a post on Mr. Excel forum. Sub NameRange() Dim RowRange As Long For RowRange = 1 To 15 Cells(RowRange, "A").Name = "Results" & RowRange Next End Sub I'd like to learn how this can be converted into a function with the following parameters * numStartRow : the row...
  6. N

    Run macro on SPECIFIC cell change

    I have cell A1 (named ObjTotal) which is sum of B1, B3, B5, B7. If A1 is <100 or >100, then I want to have a Message Box telling the user, to change the value of the B1, B3, B5 or B7 such that the sum = 100. How can I write a VBA for this. TIA. Regards, Ninad.
  7. N

    Aging analysis

    I need to do aging analysis of issues raised during internal audits. I have a sheet with all the required fields one of which is the "days". I usually did the aging for 0-30, 31-60, 61-90 and so on as per my "wish". I used nested IFs to achieve this which is then used as data source for a pivot...
  8. N

    Getting chart range

    Hi, I have PLENTY of charts in one workbook which are the same, except that the data ranges are different (same X-axis and same number of series and type). Since I do not want to physically check each graph for the range (just to make sure there's no error in a wrong range being selected)...
  9. N

    Which graph type ?

    For some reasons, the metrics for our performance measures are set into bandwidths and one example is Unsatisfactory <=19%, Acceptable >=20% and <=29%, Excellent >=30%. The data is captured and represented either monthly / quarterly. What is the best way to represent this...
  10. N

    Dependent drop down combo box. (see attached file)

    In the file I have the values for the combo box for sheet "6a" stored in the worksheet "Weightages" in a range named "Scores" (cells C29:C49). Based on the cell value E73 in worksheet 6a, I want to control the list values in the drop down as seen in rows 97 and 101 only. For e.g. if E73 is 30...
  11. N

    Concatenate type function but with a difference

    I would like to Add the data in 2 or 3 or 4 cells into a single cell and Concatenate or using the "&" will work fine if the result was in another cell. E.g. if A1 = "the", B1 = "Big" and C1 = "Fight" then Cancatenate or "&" in D1 will give the result "the big fight". How do I achieve this same...
  12. N

    Combo Box list based on value in another cell

    I have a combo box called "cmbTotal" (ActiveX control and not Forms) with list values 0,5,10,15,20,............100 which is a range named Scores. Based on a cell "A1" with range name TrendLimitValue which can take value from 0,5,10,15,20,............100, I want to limit the choice the list of...
  13. N

    Working days with Fridays and Saturdays as Offs

    Hi, I am based in Dubai where Friday and Saturday are the weekly Offs. Some have only Fridays. Networkdays calculates working days with Sat/Sun as Offs. Can someone document a function to incorporate Fri/Sat as Offs AND Fri as Off. To make it really flexible, a function outputting the...
  14. N

    Do not display IF ZERO

    Occassionally, I need to use the formula =if(A1-B1=0, "", A1-B1) in C1; A1,B1,C1 being any cell or A1-B1 representing any formula and C1 representing target cell. It is tiresome to edit each required cell with the if statement. Can someone write a VBA for this. This function, let's call it...
  15. N

    Display record (from a set of records using drop down)

    Hi, We use XL to capture client feedback. All responses are captured in an XL worksheet row-by-row with column headings being the question nos. Each record is unique to each survey. My problem is: Based on a user selectable record via a Combo Box (pertaining to a particular survey), display...
Back
Top