+ Reply to Thread
Results 1 to 5 of 5

using indirect and index/match across worksheets

  1. #1
    Registered User
    Join Date
    08-08-2013
    Location
    Miami
    MS-Off Ver
    Excel 2010
    Posts
    9

    using indirect and index/match across worksheets

    Hi!
    I have 10 worksheets, each is named for the company we manage.
    B380 - B435 are the different payment methods
    Columns H - S are YTD total, balance and the months of the year starting with July

    B C D E F G H I J K L M..... S
    380 OCA Amount YTD Paid Balance July Aug Sept Oct Nov Dec
    381 MHA18 10000 5000 5000 1000 1000 1000 500 500 1000
    382 MHA70 20000 10000 10000 2000 2000 2000 1000 1000 2000

    all the sheets have the same set up in the same rows and columns.

    in Sheet 11 (payment summary)
    I want to create a summary of payments for the specific month.


    July Abriendo Agape Banyan Camillus CARE Carrfour Catholic CFCE CHI Citrus C-FACT Concept CTC
    OCA
    MHA18 1000
    MHA70 2000


    this formula works with indirect reference to the row, sheet name and the specific column, but I would have to manually update the column every month (the R6) cell.
    VLOOKUP($A31,INDIRECT("'"&E$17&"'!A386:T395"),$R$6,FALSE)

    Since someone else will be using the worksheet, I would like it to be dummy proof so he doesn't mess this up.
    I want to so that the top corner where is says July, if I updated that every month to the corresponding month name, the values will change automatically.

    I figure there is an index/match function but not sure how to include it.

    THANK!!!

  2. #2
    Valued Forum Contributor
    Join Date
    01-10-2012
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    651

    Re: using indirect and index/match across worksheets

    can you upload a sample?

  3. #3
    Valued Forum Contributor
    Join Date
    01-10-2012
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    651

    Re: using indirect and index/match across worksheets

    at a glance I think you can just use month($R$6) instead of just $R$6 and then you can put a date in R6 and your vlookup will pull data from that many columns over...

    Might have to do a constant + or - the month value to get it lined up right...

  4. #4
    Registered User
    Join Date
    08-08-2013
    Location
    Miami
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: using indirect and index/match across worksheets

    here's a sample!

    sample.xlsx

  5. #5
    Valued Forum Contributor
    Join Date
    01-10-2012
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    651

    Re: using indirect and index/match across worksheets

    do you have a sample with your attempt at 'Payment Summary'? It looks like maybe you're only trying to return payment methods for which at least one of the companies has a payment (which would be pretty complicated...) If you're ok with having lots of blanks in there I think the attached gets it done using the following formula ... my 'Payment Summary' tab is set up differently from yours though...
    Please Login or Register  to view this content.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 7
    Last Post: 01-10-2017, 11:11 PM
  2. Index? Indirect? Match?
    By CpnVenice in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-13-2013, 02:43 PM
  3. Problem with INDEX, INDIRECT, MATCH, MATCH
    By JO505 in forum Excel General
    Replies: 5
    Last Post: 09-01-2011, 05:51 PM
  4. Index/Indirect/Match Help
    By robertsclark in forum Excel General
    Replies: 2
    Last Post: 04-20-2011, 10:24 AM
  5. Using INDEX/MATCH with indirect
    By jdpjtp910 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-15-2010, 11:39 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1