trying to create a link to a link in Excel (2007)

  • Thread starter Thread starter Jeff Mitchell
  • Start date Start date
J

Jeff Mitchell

I am hoping somebody can help me resolve a problem I am having in Excel 2007.
I have a spreadsheet that has a worksheet for each month of the year( ill call them Jan, Feb, Mar, etc..) and, in the same spreadsheet, I have worksheets that track accounts (acc1, acc2, acc3, etc).
I refer to each of the account(acc) worksheets in each of the month worksheets and I use hyperlinks to jump to these worksheets but, I would like to have a hyperlink in each of the acc(x) worksheets that returns me back to the current month worksheet.
for the sake of simplicity in explaining what I need I will say that I wantto create a separate worksheet that has a cell that I can manually change monthly that points to the current month worksheet, I could then have all the hyperlinks in each of the acc(x)
worksheets point to this cell which will redirect (hyperlink) to the correct month worksheet. Is this possible and how would I go about it. Thanks In advance! ~Jeff
to clarify, I want to avoid having to go into each account worksheet every month and change each worksheet's hyper link to point to the next monthly worksheet at the beginning of every month. I would rather change the one pointer worksheet cell to be the pointer to this worksheet and point all my acc spreadsheet links to this cell.

.. Thanks In advance! ~Jeff
 
I am hoping somebody can help me resolve a problem I am having in
Excel 2007. I have a spreadsheet that has a worksheet for each month
of the year( ill call them Jan, Feb, Mar, etc..) and, in the same
spreadsheet, I have worksheets that track accounts (acc1, acc2, acc3,
etc). I refer to each of the account(acc) worksheets in each of the
month worksheets and I use hyperlinks to jump to these worksheets
but, I would like to have a hyperlink in each of the acc(x)
worksheets that returns me back to the current month worksheet. for
the sake of simplicity in explaining what I need I will say that I
want to create a separate worksheet that has a cell that I can
manually change monthly that points to the current month worksheet, I
could then have all the hyperlinks in each of the acc(x) worksheets
point to this cell which will redirect (hyperlink) to the correct
month worksheet. Is this possible and how would I go about it.
Thanks In advance! ~Jeff to clarify, I want to avoid having to go
into each account worksheet every month and change each worksheet's
hyper link to point to the next monthly worksheet at the beginning of
every month. I would rather change the one pointer worksheet cell to
be the pointer to this worksheet and point all my acc spreadsheet
links to this cell.

. Thanks In advance! ~Jeff
Click to expand...

Have a look at the INDIRECT() function...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Have a look at the INDIRECT() function...



--

Garry



Free usenet access at http://www.eternal-september.org

Classic VB Users Regroup!

comp.lang.basic.visual.misc

microsoft.public.vb.general.discussion
Click to expand...


Hi Gary and thanks for the reply. I have played around with this function but I am having trouble turning the indirect ref into a usable link;
In a practice workbook, I used sheet4 A1 Cell and put JAN (tried both a quoted string and a literal string) and created a worksheet named "JAN".
I then created an indirect reference to it. the function displays my link as JAN!R1C1 but when I click on it I get "cannot open the specified file" dialog box.

=HYPERLINK((INDIRECT("Sheet4!R1C1",FALSE))&"!R1C1")

Any suggestions?

Thanks, Jeff
 
I don't do this in this manner because I normally setup months as
columns on a single sheet, then use a summary sheet to collect account
data in the form of a P&L layout that allows expanding/collapsing each
account for viewing/hiding details. (I find this much easier than using
separate sheets for each month!)

In the case where what you're trying to do you might be better off
using a global scope defined name and set its RefersTo to the current
month sheet. This will allow users to select the name from the NameBox
dropdown, obviating the need for a hyperlink cell...

Select the target cell to jump to in current month sheet;
In the namebox of that sheet type CurrentMonth and press Enter;
Select this name from any sheet and that's where you navigate to!

Edit the sheetname in the RefersTo when a new month sheet becomes the
CurrentMonth.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Back
Top