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


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
 
Ad

Advertisements

G

GS

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

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
 
J

Jeff Mitchell

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


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
 
Ad

Advertisements

G

GS

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top