Posts by AAE

  • Pivot Table: How to hide individual rows but keep Grand Totals

    Re: Pivot Table: How to hide individual rows but keep Grand Totals


    A sample workbook would help along with a clear explanation of the logic to be applied for which rows ought to get hidden.
    Lacking that . . .


    Unless you are looking for a VBA solution I think you are essentially stuck with manually hiding the rows (assumes none of the built in filters yield the results you want).
    However, one possibility is to use a helper column (can be virtually anywhere on the sheet if you want it out of the way) with a formula that tests for a value in the applicable column of the PT.
    So, if PT values to be0 tested are in column-A, you could use something along the lines of =IF(A5=X,TRUE,FALSE), where X="the value to test". You could then filter the helper column per your needs.

  • Dynamic Named Range (Rows and Columns)

    Re: Dynamic Named Range (Rows and Columns)


    Assuming you have version 2007 or higher, another option is to convert the data range to an Table - besides being automatically treated as dynamic ranges Tables themselves automatically carry forward cell formats and formulas to new rows as they are added. If you're not familiar with Excel Table a quick search will turn up plenty of results.

  • Sum table of values using date or year variable

    Re: Sum table of values using date or year variable


    Atomic Sheep,


    Welcome to Ozgrid.


    Because they are used in forum searches thread titles should very precisely describe the thread.
    "SUMIF" is too generic to be useful as a thread title. Note the new title I've given to your thread and in the future give more thought to creating a more precise title for your thread.


    Have you considered using a Pivot Table?


    I think your first action ought to be putting the data sheet into a classic database format, which is simply this:
    Top row = header row with each cell acting as a data field label (e.g. date, category, amount, etc.)
    Rows below header = records


    Using a proper structure for the raw data will greatly facilitate any kind of analysis/reporting you need, especially useful for pivot tables.

  • Dependent lists using data validation or comboboxes

    Re: Dependent lists using data validation or comboboxes


    sbrajagopal7,


    Did you read the forum rules? I guess not . . . otherwise you would have known that cross posting is a rule violation.
    Per the rules you agreed to abide by you must include links to *ALL* other forums where you have posted this same question.


    Click on "[COLOR="#FF0000"]Message to Cross Posters[/COLOR]" in my signature to understand why all Excel forums have this rule about cross posting.


    Regarding thread titles - note the change to your thread title. Titles should accurately describe your thread. Please avoid the use of abbreviations (Cobo) and any non-essential verbiage that dilutes the title.

  • Keep only data in square brackets

    Keep only data in square brackets


    DandM,


    Welcome to Ozgrid.
    Please reserve use of code tags only for VBA code - do not apply these tags with regular text or formulas.
    Tags are removed from your post.


    Regarding thread titles - avoid the use of special characters like [ ] - these are not search friendly.


    Thanks.

  • Conditional Formatting - 1 Rules across multiple sheets ?

    Re: Conditional Formatting - 1 Rules across multiple sheets ?


    Jack058,


    The forum rules are clear about cross posting. Did you read them?
    All Excel forums have this same rule about cross posting, for good reasons which are explained quite well in Message to Cross Posters (click link in my signature).


    The quickest way to get banned in any of the forums is to violate this rule.


    Update your thread to include any other forums where this same question is asked.

  • Conditional Formatting - 1 Rules across multiple sheets ?

    Re: Conditional Formatting - 1 Rules across multiple sheets ?


    You could use the formula option and a formula similar to this:


    =OR(COUNTIF(SHEET1!,R3:R47,A1)>0,COUNTIF(SHEET2!,R3:R47,A1)>0,COUNTIF(SHEET3!,R3:R47,A1)>0,COUNTIF(SHEET4!,R3:R47,A1)>0)


    If your ranges on the sheet will be expanding, consider creating dynamic named ranges and use them in place of hard-coded ranges.


    =OR(COUNTIF(List1,A1)>0,COUNTIF(List2,A1)>0,COUNTIF(List3,A1)>0,COUNTIF(List4,A1)>0)


    Where "List1" is the dynamic named range for names list on sheet1, "List2" is for Sheet2 names, etc.

  • Conditionally calculation based on other cell value

    Re: Conditionally calculation based on other cell value


    Welcome to Ozgrid.


    If you want to show 134.56 if D24 is less than 15000 then just reverse your true & false values.


    =IF(D24 < 15000, 134.56, ABS(D24) * 0.0089)


    =IF(A,B,C)


    where
    A = condition to test (is D24 < 15000 ?)
    B = value to show if A is True
    C = value to show of A is False


    If he above isn't what you wanted, post a sample workbook and explain more clearly with examples of the desired results.

  • VBA Code loop to compare cells generates subscript out of range error

    Re: VBA Code loop to compare cells generates subscript out of range error


    bezacom,


    When giving threads a title avoid commentary like "Tried everything and . . . ".
    Thread titles are used in searching forum - useless verbiage dilutes the search results, so be precise and stick to wording that describes the problem/need. Note the revision to your thread title.

  • populate a table based on two drop downs

    Re: populate a table based on two drop downs


    Welcome to Ozgrid.


    I don't think you've provided enough detail for a solution to be given.
    It is best to upload a workbook (dummy data) so that others can see the tables and named ranges.

  • Allow certain text in cells as well as allowing/disallowing fillings cells

    Re: Allow certain text in cells as well as allowing/disallowing fillings cells


    Quote

    AAE: Before I try this method, I think you misunderstood me. What I am trying to do is limit to how many “Y” can be entered per set of options.


    Yep, misread it. Hate it when that happens. :redface:


    However, this does what you asked, no VBA (but doesn't handily clear the cells as VBA can do).


    Create the named ranges as described in prior post, except for the "Lock" range use a single cell with "N" as a one-item list.
    I've renamed "MyList" to "YN"


    Data Validation, List option, this formula: =INDIRECT(IF(COUNTIF($D2:$F2,"Y")=0,"YN","Lock"))


    User may initially select Y or N from the drop down list for any of the cells. Once the initial "Y" selection is made the user may only enter "N" or select it from the drop down (it will be the only item on the list). User will have to manually clear the "Y" cell to regain the Y,N drop down in the other cells.

  • MsgBox / VLookUp

    Re: MsgBox / VLookUp


    Glad it is solved, now . . .


    What was the solution? For the sake of others finding this thread, please post all of the details.
    Thanks.

  • Allow certain text in cells as well as allowing/disallowing fillings cells

    Re: Allow certain text in cells as well as allowing/disallowing fillings cells


    See if this will work for you - no VBA required.


    Create a named list with two items (Y,N), Say we used cells G1:G2 and name the list "MyList"
    Create a second named list of one blank cell. Say we use H1 and name this "Lock"


    Assuming column-A holds the "Type" and column-B cells contain the drop down validation list:
    Set cell B1 data validation to use List option and refer to MyList as the source.
    Set cells B2:Bxx data validation to use Custom option and this formula:=INDIRECT(IF(OR(B1="Y",B1="N"),"MyList","Lock"))


    B2:Bxx cells will not display the Y,N drop list unless the previous cell contains an entry. The cell is effectively locked until the preceding cell is populated and will only display/allow Y,N values when conditions are met