Link to a changing cell? You'll see...

M

mevetts

Hi,

Is it possible to have a hyperlink between two sheets that jumps to th
cell that is displaying today's date?

I want to have a link from one worksheet, that when pressed takes me t
a different worksheet, but on the this new sheet locates the cell whic
is displaying today's date.

Essentially, I have a link going to my year planner, but rather tha
having to constently scroll down each time, it would be good if the ro
which contained today was immediately displayed.

The dates are all in column A.

Can this be done?

Thanks,

Mark
 
P

Pete_UK

I've assumed that your dates start at A2 in a sheet called "planner",
and in A1 you have the formula:

=MATCH(TODAY(),A2:A366)+1

This tells you which row is used for today.

Elsewhere (in the other sheet) you would enter in a suitable cell the
following formula:

=HYPERLINK("#planner!A"&planner!A1,"year planner")

Then when you click on this cell (which will display the text "year
planner") you will jump to the correct row in column A of the planner
sheet. Obviously your names might be different, but avoid names with
spaces in, as you have to include apostrophes in the formula above.

Hope this helps.

Pete
 
P

Pete_UK

I responded to this an hour ago - where's my reply gone? If it doesn't
show up in the morning I'll re-post (time for bed now).

Yes, it can be done.

Pete
 
P

Pete_UK

Oh well, I see it finally turned up! Anyone know why there should have
been such a delay?

Pete
 
D

Dave Peterson

I was just searching google and tried to go to the second results page. Google
couldn't continue--there was some message displayed that Google was getting hit
hard by some viruses and I should try later.

Maybe google had problems when you tried to post.

As an alternative (if you can...)

Saved from a previous post:

If you have Outlook Express installed, try clicking on these links (or copy and
paste into MSIE).

news://msnews.microsoft.com/microsoft.public.excel.setup
news://msnews.microsoft.com/microsoft.public.excel.misc
news://msnews.microsoft.com/microsoft.public.excel.worksheet.functions
news://msnews.microsoft.com/microsoft.public.excel.newusers
news://msnews.microsoft.com/microsoft.public.excel.programming

(and a few more for MSWord)
news://msnews.microsoft.com/microsoft.public.word.docmanagement
news://msnews.microsoft.com/microsoft.public.word.word97vba
news://msnews.microsoft.com/microsoft.public.word.newusers
news://msnews.microsoft.com/microsoft.public.word.pagelayout
news://msnews.microsoft.com/microsoft.public.word.vba.addins
news://msnews.microsoft.com/microsoft.public.word.vba.beginners
news://msnews.microsoft.com/microsoft.public.word.vba.customization
news://msnews.microsoft.com/microsoft.public.word.vba.general
news://msnews.microsoft.com/microsoft.public.word.vba.userforms
news://msnews.microsoft.com/microsoft.public.word.word6-7macros

(You can always connect to more later)

Here are some links that explain it better:

Chip Pearson has some notes written by Leonard Meads at:
http://www.cpearson.com/excel/DirectConnect.htm

David McRitchie's notes at:
http://www.mvps.org/dmcritchie/excel/xlnews.htm
http://www.mvps.org/dmcritchie/excel/oe6.htm
http://www.mvps.org/dmcritchie/excel/oe6nws01.htm

Tushar Mehta's notes at:
http://www.tushar-mehta.com/misc_tutorials/oe_ng/index.htm

And if you're looking for old posts:

Or you can use google to search for stuff you've posted (and find the replies,
too)

http://groups.google.com/advanced_group_search
http://groups.google.com/advanced_group_search?q=group:*Excel*&num=100

Ron de Bruin has an excel addin that you may like:
http://www.rondebruin.nl/Google.htm
 
Top