 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / October 2007How to make worksheet name as variable?
In my workbook, some formulas are like this: =SUMPRODUCT(($E2<'sheet01'!$G$1:$G$500)*($F2>'sheet01'!$G$1:$G$500)*($C2='sheet01'!$E$1:$E$500)*($B2='sheet01'!$F$1:$F$500),'sheet01'!$J$1:$J$500) 'sheet01' is the name of one of my worksheets. It appears several time in the worksheet. When I want to make them refer to another worksheet, I have to change many instances of the name. Can I input the worksheet name into one of the cells, and the formulas get the name from the cell, so that I just have to change it once? Thanks in advance!! Hello Eric Yes you can use the INDIRECT function to build your reference. But you will have to be careful about the (')s preceding and finishing the sheet name. eg With just the sheet name in B1 =INDIRECT("'" & B1 & "'!$G$1:$G$500) HTH Cordially Pascal > In my workbook, some formulas are like this: > [quoted text clipped - 8 lines] > > Thanks in advance!! Eric To allow a better visibility in your final formula, you could also consider defining a name for the indirect formula. So the defined name could be something like : MyShName and would refer to: =INDIRECT("'" & B1 & "'!$G$1:$G$500) And since there are multiple ranges refereing to this sheet, you could add other names to refer to those specific ranges eg: MyShNameRng1 : =INDIRECT("'" & B1 & "'!$G$1:$G$500) MyShNameRng2 : =INDIRECT("'" & B1 & "'!$E$1:$E$500) MyShNameRng3 : =INDIRECT("'" & B1 & "'!$F$1:$F$500) MyShNameRng4 : =INDIRECT("'" & B1 & "'!$J$1:$J$500) But this is only a suggestion ;-) HTH Cordially Pascal > Hello Eric > Yes you can use the INDIRECT function to build your reference. [quoted text clipped - 20 lines] >> >> Thanks in advance!! You helped me a lot!! Thank you very much. "papou" <cpapoupasbon@çanonpluslaposte.net> ¼¶¼g©ó¶l¥ó·s»D:%23Iflrc%23CIHA.4308@TK2MSFTNGP06.phx.gbl... > Eric > To allow a better visibility in your final formula, you could also [quoted text clipped - 39 lines] >>> >>> Thanks in advance!! My Pleasure ;-) Thanks for your feedback. Cordially Pascal > You helped me a lot!! > Thank you very much. [quoted text clipped - 44 lines] >>>> >>>> Thanks in advance!! |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.