+ Reply to Thread
Results 1 to 6 of 6

how to combine sheets into one sheet INDIRECT and other? functions?

  1. #1
    Registered User
    Join Date
    03-28-2016
    Location
    Ontario, Canada
    MS-Off Ver
    2010
    Posts
    3

    Post how to combine sheets into one sheet INDIRECT and other? functions?

    Hi,
    I"m new to this forum. and here's my first question.
    I have a large spreadsheet with multiple sheets - hundreds. SHEET1, SHEET2, SHEET3, etc (not these consecutive names though)
    I have a master sheet which takes in information from all sheets. I would like to use a cell reference with the sheet name and the the cells, say around 10 columns and 200 rows. (so I need to make a formula for cell one that can be copied across and down to make the thing happen as a common formula for all cells. I think I'm half the way there now, but am stuck.
    in the master sheet I have a cell in Column A, starting in row 1, with the entry "SHEET1" which I have copied down 200 rows. next I have "SHEET2" which is copied the next 200 rows.
    In column B, i made a formula "=INDIRECT($A1&"!A1")" to go and grab the cell value in "SHEET1" Cell A1, this works for SHEET2, etc.
    However.... T0his formula half works, because, when I copy it across the columns and down the columns, it still references cell A1, for all. I want it to do the array of cells:
    A1, B1, C1, etc
    A2, B2. C2 etc
    but it doesnt work and i get
    A1, A1, A1
    A1, A1, A1,
    I realize that the problem is that i told the function to do exactly this.... I can't figure out how to make it copy across and down like it normally would.
    I ultimately want column A of the master sheet to simply have the sheet names down it say 1-200 are SHEET1, and 201 to 400 are SHEET2, etc, then the cell entries in the next 10 columns are simply formulas so that they can be copied all the way down to infinity.
    Thanks, and I hope this is clear

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: how to combine sheets into one sheet INDIRECT and other? functions?

    Try this...

    =INDIRECT($A1&"!"&CELL("address",A1))

    Note that this is not robust. New row/column insertions could cause the formula to return incorrect results.

    There is not a robust solution to this problem.
    Last edited by Tony Valko; 03-28-2016 at 10:31 PM.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Registered User
    Join Date
    03-28-2016
    Location
    Ontario, Canada
    MS-Off Ver
    2010
    Posts
    3

    Re: how to combine sheets into one sheet INDIRECT and other? functions?

    Thanks
    Appears to work, I'll have a look at it again later tonight and try to understand the functions you added.
    Great resource here on excel.

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: how to combine sheets into one sheet INDIRECT and other? functions?

    Good deal. Thanks for the feedback!

  5. #5
    Registered User
    Join Date
    03-28-2016
    Location
    Ontario, Canada
    MS-Off Ver
    2010
    Posts
    3

    Re: how to combine sheets into one sheet INDIRECT and other? functions?

    Hi Again Tony,
    I got another question for the same spreadsheet file. I have about 200 sheets that each use a web query. as more sheets are added, when I use the Refresh All button it has crashed, but not now and is just slow. I looked on the web before and found that there is some limit of 30 sheets that it can handle easily and there was some code provided to clear some sort of excel track memory or something (I"m not a coder nor PC/IT guy). i stuffed the one line bit of code into a macro that I was using and it seemed to stop the crashing, but it is still slow (I don't use the macro anymore, maybe it was just luck or something else). Are there any other solutions to this to make it faster? currently takes 30 minutes to update, where I think it should only take 1 or 2.
    Thanks in advance, Paul

  6. #6
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: how to combine sheets into one sheet INDIRECT and other? functions?

    Sorry, I have no experience with web queries.

+ 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: 0
    Last Post: 10-12-2015, 10:02 AM
  2. [SOLVED] Combine Sheet Range SUM and INDIRECT
    By natefarm in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-06-2014, 01:22 PM
  3. Replies: 5
    Last Post: 07-01-2014, 05:37 AM
  4. Where do we combine INDEX and INDIRECT functions?
    By BIJALRADIA in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-01-2013, 05:48 AM
  5. Combine INDEX+MATCH functions with INDIRECT formula
    By ABSTRAKTUS in forum Excel General
    Replies: 4
    Last Post: 04-12-2011, 04:16 PM
  6. How to combine 2 sheets into one sheet
    By shahrulnashriq in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-24-2010, 05:31 AM
  7. Indirect - combine two cells to look up sheet name for formula
    By lou031205 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-02-2007, 08:03 AM

Tags for this Thread

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