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 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
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!"&
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/
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
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.
ReplyDeleteIndeed, 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.
Thanks Eduardo:
ReplyDeleteI 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