+ Reply to Thread
Results 1 to 8 of 8

Index/Match - Retrieve data across unique sheets with unique column identifiers

  1. #1
    Registered User
    Join Date
    06-17-2014
    Location
    Melbourne, Ozstraya
    MS-Off Ver
    2010
    Posts
    5

    Index/Match - Retrieve data across unique sheets with unique column identifiers

    G'day

    First post here.

    Trying to use INDEX/MATCH to retrieve data, whilst using INDIRECT to incorporate the unique sheet numbers (approx 30 sheets) & unique project numbers (approx. 120 in total)

    Please see attached for example with 2 sheets: 1) Summary sheet & 2)

    The source data needs to be retrieved from Sheet 7553 below onto the Summary sheet. I need to prepare retrieve formulas for cells C2 & C5 (FTE data) and D2, D3, D5 & D6 (Budget $)

    (Note)
    1. the reconciliation has the Budget FTE (Full time equivalent - staff numbers) on the X-axis & the response provided has the Budget on the Y-axis. Hoping this does not pose a problem)
    2. I only need to retrieve data where there is where there is a project number populated in Row 2. However, the formula needs to be intuitive enough to capture up to ten (10) projects that need to be retrieved.

    Solution?
    I started out thinking I could use INDEX/MATCH, however I can't seen to crack the issue of having the varying volume of unique projects to retrieve onto the Summary sheet.

    I was thinking INDIRECT would also be handy, however; not sure how to incorporate this in a dynamic range of unique project numbers. Perhaps a completely different approach is required? I'm all ears!

    I've got myself into a tangle & grateful for any suggestions, including overhauling the approach to the reconciliation on the summary sheet.

    Thanks in advance legends!
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,883

    Re: Index/Match - Retrieve data across unique sheets with unique column identifiers

    Hi and welcome to the forum

    You were not too clear as to which row/s you wanted to return, so I just picked row 14 so you can see the Project No....
    =INDEX(INDIRECT("'"&$C11&"'!$K:$Q"),14,MATCH(Summary!H11,INDIRECT("'"&$C11&"'!$K$14:$Q$14"),0))

    Hopefully that will get you started in the right direction?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    06-17-2014
    Location
    Melbourne, Ozstraya
    MS-Off Ver
    2010
    Posts
    5

    Re: Index/Match - Retrieve data across unique sheets with unique column identifiers

    Hi Ford

    Thanks for your reply mate: top work!

    My apologies. Need to retrieve the following rows:

    29 = FTE data
    39 = Basic cost $
    59 = Discretion cost $

    Therefore, FTE data =INDEX(INDIRECT("'"&$C12&"'!$K:$Q"),29,MATCH($H12,INDIRECT("'"&$C12&"'!$K$14:$Q$14"),0))

    Can I add 3 IF statements in place of the row array (Row 29 in the formula above) to reflect the required row references above?

    Further, for the purpose of skipping blanks when copying the formula from rows 11:22; how would you manipulate the formula to exclude blanks? IF(ISBLANK)?

    =IF(ISBLANK(INDEX(INDIRECT("'"&$C12&"'!$K:$Q"),29,MATCH($H12,INDIRECT("'"&$C12&"'!$K$14:$Q$14"),0)))," ",INDEX(INDIRECT("'"&$C13&"'!$K:$Q"),29,MATCH($H13,INDIRECT("'"&$C13&"'!$K$14:$Q$14"),0)))

    Thanks very much!

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,883

    Re: Index/Match - Retrieve data across unique sheets with unique column identifiers

    I dont see any unique identifier to find each of those rows

    So based on that, all I can suggest is to use the actual row number and copy down with discretion.

    just change the bolded part here...
    =INDEX(INDIRECT("'"&$C11&"'!$K:$Q"),29,MATCH(Summary!H11,INDIRECT("'"&$C11&"'!$K$14:$Q$14"),0))

    If you could keep your headings/titles the same on both sheets, we could probably ius ethat to automate the row number.

    See what I have so far...

    Also, for a total by project, you could use this...
    =SUBTOTAL(9,OFFSET('7553'!$K$14,0,MATCH(Summary!$H11,'7553'!$K$14:$Q$14,0)-1,45,1))
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    06-17-2014
    Location
    Melbourne, Ozstraya
    MS-Off Ver
    2010
    Posts
    5

    Re: Index/Match - Retrieve data across unique sheets with unique column identifiers

    Thanks mate. Really helpful!

    Added in 3 unique identifiers in columns B, C & D on Summary! sheet & used formula:

    =INDEX(INDIRECT("'"&$E11&"'!$K:$Q"),MATCH($B11,'7553'!$A:$A,0),MATCH($J11,INDIRECT("'"&$E11&"'!$K$14:$Q$14"),0))

    However this poses problems when trying to retrieve Columns B & C of Summary! sheet. Any suggestions?


    I worked through the formula =SUBTOTAL(9,OFFSET('7553'!$K$14,0,MATCH(Summary!$I11,'7553'!$K$14:$Q$14,0)-1,45,1))

    This will capture $K$14:$K$58. Is there a way this can be changed to capture $K$33:$K$58? As I do not need to include FTE amounts in this calculation.

    Or should I just include another formula & limit the OFFSET to capture $K14:$K$29 - as per Column N in Summary!?

    Sorry, I inherited the templates for example response 7553 & they are not very helpful at all!
    Attached Files Attached Files

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,883

    Re: Index/Match - Retrieve data across unique sheets with unique column identifiers

    No, what I meant was on Summary, you have these headings...
    G10=General Operating Costs
    G15=Discretionary Costs
    G19=Total Project Cost

    What do they relate to on 7553, because there you have...
    C29=Blank
    C39=TOTAL Basic:
    C59=TOTAL Discretionary

  7. #7
    Registered User
    Join Date
    06-17-2014
    Location
    Melbourne, Ozstraya
    MS-Off Ver
    2010
    Posts
    5

    Re: Index/Match - Retrieve data across unique sheets with unique column identifiers

    Change headings on Summary Sheet to align with 7553:
    TOTAL Basic
    TOTAL Discretionary
    TOTAL Project Costs

    C29 is blank but is TOTAL FTE, where Total FTE is to be reported in Column L on Summary sheet

  8. #8
    Registered User
    Join Date
    06-17-2014
    Location
    Melbourne, Ozstraya
    MS-Off Ver
    2010
    Posts
    5

    Re: Index/Match - Retrieve data across unique sheets with unique column identifiers

    Should also point out that the names are interchangeable so I'm open to most simplest suggestion. As I mentioned earlier the example 7553 sheet can be manipulated with headings as required..

+ 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. Sorting based on unique identifiers
    By excel_beginner2 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-04-2014, 10:09 AM
  2. Transpose AND compile data for unique identifiers
    By mrs179 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 10-07-2013, 01:42 AM
  3. [SOLVED] Merging two sheets with identical unique identifiers
    By shade45 in forum Excel General
    Replies: 11
    Last Post: 07-20-2012, 06:22 PM
  4. Excel 2007 : adding values with unique identifiers
    By AntiC in forum Excel General
    Replies: 3
    Last Post: 01-27-2012, 10:52 AM
  5. [SOLVED] How do I match unique identifiers on two Excel worksheets?
    By Randi Hagen,--Flagler College in forum Excel General
    Replies: 1
    Last Post: 02-03-2006, 11:10 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