Consulting

Results 1 to 7 of 7

Thread: Last Occurence of last month Transaction using Index sumproduct

  1. #1
    VBAX Contributor
    Joined
    Sep 2007
    Posts
    116
    Location

    Last Occurence of last month Transaction using Index sumproduct

    Hi,

    I try to obtain the last transaction balance of a selected currency in sheet2 from a daily transaction database in sheet1 using combination of INDEX SUMPRODUCTS and MAX but is not working.

    Date in sheet2 B2 for reporting month (March 17) and I require to obtain last occurence of the respective currency in previous month (Feb 17) from sheet 1

    sample file attached
    Attached Files Attached Files

  2. #2
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    On the same sheet for simplicity
    Attached Files Attached Files
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  3. #3
    VBAX Regular
    Joined
    Feb 2013
    Posts
    46
    Location
    The max portion of your formula is determining the sheet row number and INDEX requires the table row number.

  4. #4
    VBAX Contributor
    Joined
    Sep 2007
    Posts
    116
    Location
    I want to locate cell value in column F for a selected currency in last transaction of previous month. eg if the month in K8 is March then the last txn of the currency in Feb per highlight in green and yellow cell.
    Attached Files Attached Files

  5. #5
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Apologies, totally misunderstood.
    Give this a try
    Array formula =INDEX($F$2:$F$12,MATCH((MAX(IF(($A$2:$A$12=$J3)*($B$2:$B$12<=EOMONTH(K$1,-1)),$B$2:$B$12))),$B$2:$B$12))
    Attached Files Attached Files
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  6. #6
    VBAX Contributor
    Joined
    Sep 2007
    Posts
    116
    Location
    Thanks mdmackillop! You save my day

  7. #7
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Happy to help. I played around a bit more to implement range names making it more flexible and user friendly.
    Attached Files Attached Files
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •