 |
 | 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 / January 2008Fixing sheet names is a 3D reference
I have a workbook containing several worksheets and a summary sheet that refers to all the other sheets with a 3D reference. I need to fix the 3D reference just as I can fix a cell address by entering $A$5 (eg). So my reference would appear as =SUM($Sheet1:$Sheet10:B5) - this would allow me to delete, rename the boundary sheets without the reference being affected in the summary sheet. Is there any way to do this or otherwise protect the reference in summary sheet? Thanks No such feature in Excel versions <2007....can't speak for 2007. Try this method............... Add a new blank sheet to the right of your Summary sheet Add a new blank sheet at end. Name these Start and End. On your Summary sheet in a cell enter =SUM(Start:End!B5) This will sum all B5's on sheets between your two dummy sheets In future when adding/deleting sheets do so between Start and End. Gord Dibben MS Excel MVP >I have a workbook containing several worksheets and a summary sheet that >refers to all the other sheets with a 3D reference. I need to fix the 3D [quoted text clipped - 8 lines] > >Thanks That doesn't work for me as the first sheet behind the summary is directly referenced but it's data has to be reset each week. My own solution was to copy the "week0" sheet to before "week52", then rename "week0 (2) to "weekx" and then delete the contents of "week0" It works, but thanks anyway > No such feature in Excel versions <2007....can't speak for 2007. > [quoted text clipped - 26 lines] > > > >Thanks Hi David Post the formula that you currently are using, to see why Gord's suggestion did not work for you.  SignatureRegards Roger Govier > That doesn't work for me as the first sheet behind the summary is directly > referenced but it's data has to be reset each week. [quoted text clipped - 42 lines] >> > >> >Thanks FYI Gord, same for 2007 SignatureRegards Roger Govier > No such feature in Excel versions <2007....can't speak for 2007. > [quoted text clipped - 32 lines] >> >>Thanks |
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.