Posts by mattalin

    Referencing an old thread: http://www.ozgrid.com/forum/showthread.php?t=147605&page=1 but hoping to improve on the code.


    I used the code and it works great as long as only one excel source file is linked to the Presentation. Was hoping code could be modified to do the following (assuming there are more than one linked files):


    1. Scan the PPT file and list all external excel source files within the document.
    2. Allow the user to pick one of the existing source files.
    3. Apply the below code to change the selected old source file with the the newly selected source file.


    Re: SUMIF Function based on Row selected


    I pretty much figured out how to make a UDF out of the change event:


    Code
    1. Function SUMIFD(rngStatic As Range, Criteria As Range, rngInput As Range)
    2. Application.Volatile True
    3. If Application.Intersect(ActiveCell.EntireRow, rngInput) Is Nothing Then
    4. SUMIFD = "Out of Range"
    5. Else
    6. SUMIFD = Application.WorksheetFunction.SumIf(rngStatic, Criteria, Application.Intersect(ActiveCell.EntireRow, rngInput))
    7. End If
    8. End Function


    The only thing missing is the "Out of Range" piece doesn't seem to work correctly. I wanted to make it say "out of range" or return zero when the user selects a cell outside the intersection.

    Re: SUMIF Function based on Row selected


    Thanks, this works great for the "Worksheet_SelectionChange" event, however would it be easy to convert this code into a UDF to make it more flexible when implementing into a large spreadsheet with multiple ranges - i.e. without having to manipulate the code for each occurance?

    Re: SUMIF Function based on Row selected


    Here is what I have so far.


    Code
    1. Function SUMIFD(rngStatic As Range, Criteria As Range)
    2. Set AreaRange = ActiveCell.CurrentRegion 'Would like this to be based on Yellow-shaded
    3. 'cells (since they will start out all zero values)
    4. Set rngDynamic = Application.Intersect(AreaRange, ActiveCell.EntireRow)
    5. SUMIFD = Application.WorksheetFunction.Sum(rngDynamic)
    6. End Function


    I realize I am missing the criteria matching the static range (among other things). I would like the code to do the following:


    1) Allow the UDF to be placed in a particular cell, in this example F3.


    2) The UDF would sum the cells - that match the first criteria (FY2006) in E3, based on the range B7:R7 (rngStatic) - based on the intersection between the yellow-shaded region and active row.

    For example, if the user were to select cell B10 then the value in F3 would be ~$81. If they were to enter $10 in cell F10, then F3 would equal ~$91. If the user were to then select cell J14, then the value of F3 would show $187 and the value of F4 would show $278.

    Files

    • SUMIFD.xls

      (32.77 kB, downloaded 38 times, last: )

    I would like to create a UDF similar to the SUMIF function but the UDF would be dynamic in that it would sum based on a dynamic range that would change based on the row the user is in.


    background: I have a very large input template for 12-18 months for multiple cost categories. I would like to have the UDF in a specific cell above each category that would show the user what the sum of the range in the current row they are inputing data. Each row represents a specific project/task (along with 12-15 descriptive columns) that makes the freeze pane option unusable.