Friday, November 18, 2011

yogi_Pull Data Into SummarySheet From Client Sheets Within The Same Spreadsheet

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                  
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.

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