yogi_Pull Data Into SummarySheet From Client Sheets In Different Spreadsheets

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 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:


Earlier, assuming that Jojoba123 had SummaySheet and ClIENT sheets within the same spreadsheet I had posted a solution in yogi_Pull Data Into SummarySheet From Client Sheets Within The Same Spreadsheet