Friday, November 18, 2011

yogi_Pull Data Into SummarySheet From Client Sheets Within The Same Spreadsheet

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user Jojoba123 said:
I am currently using Google Docs to manage my clients. Every client gets their own sheet in my spreadsheet. I created a summary sheet that uses the INDIRECT function to dynamically refer to my client sheets in the spreadsheet. I am quickly approaching my sheet limit in the spreadsheet. What I want to do is to have a separate summary sheet spreadsheet and use INDIRECT to refer to  a different spreadsheet that contains my client sheets.
I have looked extensively through these forums to solve my issue, it seems like the INDIRECTfunction does not allow referring to another spreadsheet. The closes thing I found was theIMPORTRANGE function, that used part of the spreadsheet URL to link between spreadsheets. If this was Excel the indirect formula would look something like this:indirect("book1.xls!"&PitcherNameCell),30,1)The "book1.xls!" being the most important bit to refer to the workbook (in Google Docs terms: spreadsheet) that I want to get information out of.I appreciate any help, even if it is to just explain to me that Google Docs cannot do this, so that I can begin looking for another way to manage my clients.
Here is an example to see what I currently have set up, so you can get an idea of what I want to accomplish.https://docs.google.com/spreadsheet/ccc?key=0AvuVMsSOGJSwdFUxQnFHSEZlVHRmcWptcml0dlBTT0E
-------------------------------

I am not quite clear on user Jojoba123's requirements ... the user talks about different spreadsheets but in the example the user has provided the SummarySheet and the Client sheets are within the same spreadsheet. In the following solution to the problem I have assumed that the SummarySheet and the Client sheets are within the same spreadsheet

2 comments:

  1. I understood that the user is reaching the Spreadsheet limit for content, so is requesting a way to export content into another Spreadsheet so that he can continue managing his clients.

    Indeed, an *importrange* function would be needed. I suppose the user want to understand how to get the document address key to include in the function along with the range.

    ReplyDelete
  2. Thanks Eduardo:

    I now realize that despite the example spreadsheet provided by Jojoba123 where he showed the SummarySheet and the CLIENT sheet(s) within the same spreadsheet he wants to use these in different spreadsheets ... so with this as the background I have posted a solution for pulling in the CLIENT data into SummarySheet ... and with the assumptions that I made and the formulation I used I did not even have to use the INDIRECT function as shown in my blog post:

    http://yogi--anand-consulting.blogspot.com/2011/11/yogipull-data-into-summarysheet-from_4558.html

    Cheers!
    Yogi
    Cloud Computing -- Google Docs Way
    yogi--anand-consulting.blogspot.com

    ReplyDelete