Can you use INDIRECT in 3-D references?
For example
=SUM(INDIRECT(C10)) where C10 would contain ="Sheet2:"&"Sheet3!"&"A"&ROW() always returns #REF!. However, ="Sheet3!"&"A"&ROW() as the Cell C10 entry will work fine. |
Can you use INDIRECT in 3-D references?
Gdcprogrc wrote...
For example =SUM(INDIRECT(C10)) where C10 would contain ="Sheet2:"&"Sheet3!"&"A"&ROW() always returns #REF!. .... No. INDIRECT can only return range references. 3D references are never range references. You could use a list of worksheet names, e.g., WSLST referring to ={"Sheet2","Sheet3"} then use the formula =SUMPRODUCT(N(INDIRECT("'"&WSLST&"'!A"&ROW())) |
Can you use INDIRECT in 3-D references?
Hi!
Try it like this: C10 = ="A"&ROW() =SUMPRODUCT(N(INDIRECT("Sheet"&ROW(INDIRECT("2:3") )&"!"&C10))) Biff "Gdcprogrc" wrote in message ... For example =SUM(INDIRECT(C10)) where C10 would contain ="Sheet2:"&"Sheet3!"&"A"&ROW() always returns #REF!. However, ="Sheet3!"&"A"&ROW() as the Cell C10 entry will work fine. |
Can you use INDIRECT in 3-D references?
Thanks Biff. That seems to work.
What does one do if your worksheets are named, June, July, August etc.? "Biff" wrote: Hi! Try it like this: C10 = ="A"&ROW() =SUMPRODUCT(N(INDIRECT("Sheet"&ROW(INDIRECT("2:3") )&"!"&C10))) Biff "Gdcprogrc" wrote in message ... For example =SUM(INDIRECT(C10)) where C10 would contain ="Sheet2:"&"Sheet3!"&"A"&ROW() always returns #REF!. However, ="Sheet3!"&"A"&ROW() as the Cell C10 entry will work fine. |
Can you use INDIRECT in 3-D references?
What does one do if your worksheets are named, June, July, August etc.?
In that case, use Harlan's suggestion. Biff "Gdcprogrc" wrote in message ... Thanks Biff. That seems to work. What does one do if your worksheets are named, June, July, August etc.? "Biff" wrote: Hi! Try it like this: C10 = ="A"&ROW() =SUMPRODUCT(N(INDIRECT("Sheet"&ROW(INDIRECT("2:3") )&"!"&C10))) Biff "Gdcprogrc" wrote in message ... For example =SUM(INDIRECT(C10)) where C10 would contain ="Sheet2:"&"Sheet3!"&"A"&ROW() always returns #REF!. However, ="Sheet3!"&"A"&ROW() as the Cell C10 entry will work fine. |
Can you use INDIRECT in 3-D references?
Thanks, again, Biff. I was hoping not to have to use a defined name because
I wanted the name list length to change or be variable, which requires more work. But I did like Harlan's suggestion for other applications. "Biff" wrote: What does one do if your worksheets are named, June, July, August etc.? In that case, use Harlan's suggestion. Biff "Gdcprogrc" wrote in message ... Thanks Biff. That seems to work. What does one do if your worksheets are named, June, July, August etc.? "Biff" wrote: Hi! Try it like this: C10 = ="A"&ROW() =SUMPRODUCT(N(INDIRECT("Sheet"&ROW(INDIRECT("2:3") )&"!"&C10))) Biff "Gdcprogrc" wrote in message ... For example =SUM(INDIRECT(C10)) where C10 would contain ="Sheet2:"&"Sheet3!"&"A"&ROW() always returns #REF!. However, ="Sheet3!"&"A"&ROW() as the Cell C10 entry will work fine. |
Can you use INDIRECT in 3-D references?
Gdcprogrc wrote...
Thanks, again, Biff. I was hoping not to have to use a defined name because I wanted the name list length to change or be variable, which requires more work. But I did like Harlan's suggestion for other applications. .... My technique works with dynamic range names. Just make WSLST *long*, enter only the worksheet names over which you want to sum at the top of the list, and create another defined name like WSLST.EFFECTIVE (or something shorter) defined as =INDEX(WSLST,1):INDEX(WSLST,COUNTA(WSLST)) (no volatile function calls), and use WSLST.EFFECTIVE in place of WSLST. "Gdcprogrc" wrote in message .... What does one do if your worksheets are named, June, July, August etc.? .... Use TEXT(DATE(2006,{6;7;8;...},1),"mmmm") in place of "Sheet"&ROW(INDIRECT("2:3")). |
Can you use INDIRECT in 3-D references?
You can make the sheet name list a dynamic range list and use Harlan's
formula....... List the sheet names in a range of cells, say, H1:Hn Create a dynamic named range for the sheet names. Goto InsertNameDefine Name: Sheet_Names Refers to: =OFFSET(Sheet1!$H$1,,,COUNTA(Sheet1!$H:$H)) Then: =SUMPRODUCT(N(INDIRECT("'"&Sheet_Names&"'!"&C10))) Biff "Gdcprogrc" wrote in message ... Thanks, again, Biff. I was hoping not to have to use a defined name because I wanted the name list length to change or be variable, which requires more work. But I did like Harlan's suggestion for other applications. "Biff" wrote: What does one do if your worksheets are named, June, July, August etc.? In that case, use Harlan's suggestion. Biff "Gdcprogrc" wrote in message ... Thanks Biff. That seems to work. What does one do if your worksheets are named, June, July, August etc.? "Biff" wrote: Hi! Try it like this: C10 = ="A"&ROW() =SUMPRODUCT(N(INDIRECT("Sheet"&ROW(INDIRECT("2:3") )&"!"&C10))) Biff "Gdcprogrc" wrote in message ... For example =SUM(INDIRECT(C10)) where C10 would contain ="Sheet2:"&"Sheet3!"&"A"&ROW() always returns #REF!. However, ="Sheet3!"&"A"&ROW() as the Cell C10 entry will work fine. |
Can you use INDIRECT in 3-D references?
Thanks a lot, Biff. Huge help and I appreciate it!
"Biff" wrote: You can make the sheet name list a dynamic range list and use Harlan's formula....... List the sheet names in a range of cells, say, H1:Hn Create a dynamic named range for the sheet names. Goto InsertNameDefine Name: Sheet_Names Refers to: =OFFSET(Sheet1!$H$1,,,COUNTA(Sheet1!$H:$H)) Then: =SUMPRODUCT(N(INDIRECT("'"&Sheet_Names&"'!"&C10))) Biff "Gdcprogrc" wrote in message ... Thanks, again, Biff. I was hoping not to have to use a defined name because I wanted the name list length to change or be variable, which requires more work. But I did like Harlan's suggestion for other applications. "Biff" wrote: What does one do if your worksheets are named, June, July, August etc.? In that case, use Harlan's suggestion. Biff "Gdcprogrc" wrote in message ... Thanks Biff. That seems to work. What does one do if your worksheets are named, June, July, August etc.? "Biff" wrote: Hi! Try it like this: C10 = ="A"&ROW() =SUMPRODUCT(N(INDIRECT("Sheet"&ROW(INDIRECT("2:3") )&"!"&C10))) Biff "Gdcprogrc" wrote in message ... For example =SUM(INDIRECT(C10)) where C10 would contain ="Sheet2:"&"Sheet3!"&"A"&ROW() always returns #REF!. However, ="Sheet3!"&"A"&ROW() as the Cell C10 entry will work fine. |
Can you use INDIRECT in 3-D references?
Thanks a lot, Harlan, you were a big help!
"Harlan Grove" wrote: Gdcprogrc wrote... For example =SUM(INDIRECT(C10)) where C10 would contain ="Sheet2:"&"Sheet3!"&"A"&ROW() always returns #REF!. .... No. INDIRECT can only return range references. 3D references are never range references. You could use a list of worksheet names, e.g., WSLST referring to ={"Sheet2","Sheet3"} then use the formula =SUMPRODUCT(N(INDIRECT("'"&WSLST&"'!A"&ROW())) |
Can you use INDIRECT in 3-D references?
You're welcome!
Biff "Gdcprogrc" wrote in message ... Thanks a lot, Biff. Huge help and I appreciate it! "Biff" wrote: You can make the sheet name list a dynamic range list and use Harlan's formula....... List the sheet names in a range of cells, say, H1:Hn Create a dynamic named range for the sheet names. Goto InsertNameDefine Name: Sheet_Names Refers to: =OFFSET(Sheet1!$H$1,,,COUNTA(Sheet1!$H:$H)) Then: =SUMPRODUCT(N(INDIRECT("'"&Sheet_Names&"'!"&C10))) Biff "Gdcprogrc" wrote in message ... Thanks, again, Biff. I was hoping not to have to use a defined name because I wanted the name list length to change or be variable, which requires more work. But I did like Harlan's suggestion for other applications. "Biff" wrote: What does one do if your worksheets are named, June, July, August etc.? In that case, use Harlan's suggestion. Biff "Gdcprogrc" wrote in message ... Thanks Biff. That seems to work. What does one do if your worksheets are named, June, July, August etc.? "Biff" wrote: Hi! Try it like this: C10 = ="A"&ROW() =SUMPRODUCT(N(INDIRECT("Sheet"&ROW(INDIRECT("2:3") )&"!"&C10))) Biff "Gdcprogrc" wrote in message ... For example =SUM(INDIRECT(C10)) where C10 would contain ="Sheet2:"&"Sheet3!"&"A"&ROW() always returns #REF!. However, ="Sheet3!"&"A"&ROW() as the Cell C10 entry will work fine. |
Can you use INDIRECT in 3-D references?
Harlan,
how do you create a list of worksheet name? I tried Insert - Names - Define , but I cannot physically enter the list of sheets names I want in the Refers to entryfield because I don't know how to format it properly. Should it be simply a reference to a cell containing the list of worksheets? Thanks, Stefano "Harlan Grove" wrote: Gdcprogrc wrote... For example =SUM(INDIRECT(C10)) where C10 would contain ="Sheet2:"&"Sheet3!"&"A"&ROW() always returns #REF!. .... No. INDIRECT can only return range references. 3D references are never range references. You could use a list of worksheet names, e.g., WSLST referring to ={"Sheet2","Sheet3"} then use the formula =SUMPRODUCT(N(INDIRECT("'"&WSLST&"'!A"&ROW())) |
Can you use INDIRECT in 3-D references?
Ref Harlan's ..
You could use a list of worksheet names, e.g., WSLST referring to ={"Sheet2","Sheet3"} Click Insert Name Define Copy and paste the part: ={"Sheet2","Sheet3"} directly into the "Refers to:" box Then just enter into the "Names in workbook:" box: WSLST and click OK -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "smaruzzi" wrote: Harlan, how do you create a list of worksheet name? I tried Insert - Names - Define , but I cannot physically enter the list of sheets names I want in the Refers to entryfield because I don't know how to format it properly. Should it be simply a reference to a cell containing the list of worksheets? Thanks, Stefano "Harlan Grove" wrote: Gdcprogrc wrote... For example =SUM(INDIRECT(C10)) where C10 would contain ="Sheet2:"&"Sheet3!"&"A"&ROW() always returns #REF!. .... No. INDIRECT can only return range references. 3D references are never range references. You could use a list of worksheet names, e.g., WSLST referring to ={"Sheet2","Sheet3"} then use the formula =SUMPRODUCT(N(INDIRECT("'"&WSLST&"'!A"&ROW())) |
All times are GMT +1. The time now is 04:37 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com