Posts by pangolin

    Re: Check cells content based on condition


    see if this code helps


    Re: Multiple criteria wit INDEX


    one approach


    =IFERROR(INDEX($C$1:$C$34,LARGE(ROW($A$2:$A$34)*(($A$2:$A$34)=$E2)*(($B$2:$B$34)=$F2),SUMPRODUCT((($A$2:$A$34)=$E2)*(($B$2:$B$34)=$F2))-(COLUMNS($G$1:G$1)-1))-ROWS($C$2)+1),"")


    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


    Code
    1. Sub findmod()
    2. Set myrange = Sheets("Sheet1").Range("A1", Range("A" & Rows.Count).End(xlUp))
    3. For Each cell In myrange
    4. If cell.Value Mod 3 = 0 Then cell.Offset(0, 1) = 3
    5. Next cell
    6. 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


    =SUMPRODUCT(((([wb2.xlsx]Sheet1!$F$1:$F$26)="a")+(([wb2.xlsx]Sheet1!$F$1:$F$26)="b"))*(([wb2.xlsx]Sheet1!$G$1:$G$26)=G1)*[wb2.xlsx]Sheet1!$H$1:$H$26)


    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 driven...you 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


    =IF(AND(C5>=$G$3,C5<$C$3,C7>0),MAX(0,MIN(C7,$C$1+SUM($B$9:B9)))*-1,IF(C5=$C$3,SUM($B$9:B9)*-1,0))


    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


    =IF(C5=$G$3,$C$1*-1,IF(C5=$C$3,$C$1,0))

    copy paste into other cells


    hope that helps