+ Reply to Thread
Results 1 to 7 of 7

Composite Sheet

  1. #1
    Registered User
    Join Date
    09-01-2015
    Location
    NC
    MS-Off Ver
    2013
    Posts
    11

    Composite Sheet

    I'm trying to use this formula to put all my other sheets on one master/composite sheet, and I'm having some problems.
    Could anyone help?

    I'm trying to use this formula: =INDEX(INDIRECT("'"&INT((COLUMN()-5)/12)+1&"'!E6:L49"),MATCH($A3,INDIRECT("'"&INT((COLUMN()-5)/12)+1&"'!A6:A49"),0),MATCH(E$2,INDIRECT("'"&INT((COLUMN()-5)/12)+1&"'!E5:L5"),0))
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Composite Sheet

    Im puzzled. You said that was working for you, yet you now post that you cant get it to work?

    I entered dummy data into sheets 1:13.
    I then copied that to E3, then copied it across to "day" 13 in Composite, and it pulled in all my dummy data correctly.
    What exactly are you having a problem with?
    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
    09-01-2015
    Location
    NC
    MS-Off Ver
    2013
    Posts
    11

    Re: Composite Sheet

    This is a different spreadsheet.
    I might have messed up the formula?

    nalco worksheet.png

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Composite Sheet

    OK, I have used my formula on your new sheet, and it works there too. It is giving an error on column J:R, because on your "day" sheets (all of them), J5"L5 include a date as well as a time. Remove the date from the cells and you shopuld be good.

    a quick way to do ALL sheets at the same time...
    Click on sheet 1, hold shift and click sheet 31 - you have now GROUPED all those sheets.
    Delete the date from those 3 cells - the change will be made on ALL those sheets
    Repeat for all the other times that have the date in them
    Click back onto the composite sheet and the GROUPING will be removed

    edit: Yup, I saw that problem too. It's not the formula, its the data on the day sheets

  5. #5
    Registered User
    Join Date
    09-01-2015
    Location
    NC
    MS-Off Ver
    2013
    Posts
    11

    Re: Composite Sheet

    Wow I didn't see that...Again thanks! You're a genius!

  6. #6
    Registered User
    Join Date
    09-01-2015
    Location
    NC
    MS-Off Ver
    2013
    Posts
    11

    Re: Composite Sheet

    Sorry to keep bothering you, but now I'm running into a problem where it's repeating numbers. Sheet 1 is showing up twice on the composite sheet.

    Nalco Water Chemistry.xlsx

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Composite Sheet

    OK, found the problem. Your 1st file had 12 times in it, the new file only has 8. Makes a big difference.

    Change the formula to this...
    =INDEX(INDIRECT("'"&INT((COLUMN()-5)/8)+1&"'!E6:L49"),MATCH($A3,INDIRECT("'"&INT((COLUMN()-5)/8)+1&"'!A6:A49"),0),MATCH(E$2,INDIRECT("'"&INT((COLUMN()-5)/8)+1&"'!E5:L5"),0))

+ 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. [SOLVED] Making a composite worksheet from 31 others?
    By zmurray in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-17-2015, 02:30 AM
  2. Is there a way to make a composite view of this data?
    By NewbieGuy in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 12-06-2013, 11:51 AM
  3. Composite Ranking
    By donmat in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 04-09-2013, 01:47 PM
  4. Auto-populate composite grid
    By ryancaseymcshane in forum Excel General
    Replies: 0
    Last Post: 03-08-2013, 05:45 PM
  5. Composite list
    By Willis in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-17-2012, 11:12 PM
  6. Are composite controls possible?
    By FunkyFox in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-04-2010, 06:53 AM
  7. using composite names in vlookup
    By Hein in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-27-2009, 06:01 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