Posts by pangolin

    Re: Check cells content based on condition

    see if this code helps

    Re: Multiple criteria wit INDEX

    one approach


    note this is an array formula and needs to be conformed by Ctrl+Shift+Enter

    Re: Random Team selection based on player cost, points

    try SOLVER....see the forllowing steps

    1.. Create 2 helper columns with as many rows as number of players
    2...first helper col should only contain zeros
    3...second helper col should be a product of the corresponding row of first helper col and the cost of plyers
    4...Create two sum formula one for each helper col
    5...set "Target Cell" to Value of 6 which is the sum of first helper column
    6...then set the following three constraints
    a. First helper col only to be set to Binary
    b. Sum of second helper col should be less than or equal to 15
    c. Sum of second helper col should be greater than or equal to 13

    Then you SOLVE

    I solved for the first 64 players and I got selection of player nos 2, 55, 56, 57, 59, 60 for a cost of 13

    hope that helps

    Re: VBA Code for Getting Divisior

    see this

    Sub findmod()
    Set myrange = Sheets("Sheet1").Range("A1", Range("A" & Rows.Count).End(xlUp))
    For Each cell In myrange
    If cell.Value Mod 3 = 0 Then cell.Offset(0, 1) = 3
    Next cell
    End Sub

    develop similar code for the other validations and then post back ur code for the benefit of everybody

    Re: Unique Values from multiple cells with delimiter into one cell

    you can try this code

    Re: Not to show data if cell is blank

    it is not very clear (atleast to me) what exactly you want to do but have you tried the ISBLANK function that you can use to determine which cell is blank

    my suggestion to you would be that in your post you provide only the relevant explanation instead of describing functionality of ur worksheet which may not be germane to your immediate requirements

    Re: Match data from 2 columns and shift row to align

    see if this code helps

    Re: Pulling Data from other workbooks

    this works for me


    note the syntax of using OR in SUMPRODUCT

    Re: MATCH a cell to a Range of a periodically updated list of records

    after pasting fresh set of data you have to come to this sheet and copy paste this formula to the next 20 rows and after every 20 rows the range references in the formula change as you want....

    this paste of formula is not VBA will have to do manually

    and it is as per your first post where you said that you "prefer a non vba solution"

    Re: Dynamically updating multiple scenario cashflow

    in Row 9 you can use this formula


    note this formula assumes a T=0 column which is specifically inserted and is now Col B and therefore other values have been right shifted by one column

    in Row 11 you can use this formula


    copy paste into other cells

    hope that helps