Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / February 2007

Tip: Looking for answers? Try searching our database.

Referring to other worksheets...

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
xrbbaker - 21 Feb 2007 14:41 GMT
I have a feeling that I’m making this overly complicated.  I know I could use
a Constant, but I’m trying to avoid that.

Each month a report forecasts out 30, 60, and 90 days - Feb07, Mar07, Apr07,
etc.  There are tabs for each month labeled “Feb07 Rel”, “Mar07 Rel”, etc.  
Formulae in a forecasting/summary sheet currently reference these tabs as in:

='Feb07 Rel'!C34+'Feb07 Rel'!C37
='Mar07 Rel'!C34+'Mar07 Rel'!C37
etc.

Rather than have to edit a series of cells each month – bumping Feb07 to
Mar07 and Mar07 to Apr07, I’d like to have the formulae refer to cells on
another sheet that designate which tab is the current 30 days out tab, 60
days out tab, and 90 days out tab.  That way I need only change the Referring
cell location like this:

Worksheet “Report Parms”
A            B
1    30 days out tab    Feb07 Rel
2    60 days out tab    Mar07 Rel
3    90 days out tab    Apr07 Rel

My question is, how do I refer to the content of ‘Report Parms’!$B$1 in the
formula string ='Feb07 Rel'!C34+'Feb07 Rel'!C37??  I’m trying to concatenate
‘Report Parms’!$B$1&C34 but I can’t figure out how to lay out the quotes, or
if that doesn’t work, what else is needed.

Thanks for any help.

Russ
JE McGimpsey - 21 Feb 2007 14:48 GMT
Take a look at the INDIRECT() function. For example:

   =INDIRECT("'" & B1 & "'!C34")

> I have a feeling that I’m making this overly complicated.  I know I could use
> a Constant, but I’m trying to avoid that.
[quoted text clipped - 27 lines]
>
> Russ
xrbbaker - 21 Feb 2007 15:28 GMT
Much obliged!!  I was screwing around with the Indirect function.  At least I
was in the right area...  I couldn't figure out the proper quoting nesting of
" ' " until your solution.  Thanks much!!

Russ

> Take a look at the INDIRECT() function. For example:
>
[quoted text clipped - 31 lines]
> >
> > Russ
 
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.