Embed a cell value within a link to an Excel file

Hi,

I'm trying to navigate to a specific excel file within a Windows Sharepoint site. The file that I want to navigate to is dependent on a date within a specific cell.

e.g.

Cell W34 = Value 2008-10-21

=('http://website.com/PMO/Meetings/[=Cell W34]/[Spreadsheet.xls]worksheet'!D11)

How can I write this so that the value 2008-10-21 is inserted into the link?

Cheers.
Alex

try ="('http://website.com/PMO/Meetings/"&W34&"/[Spreadsheet.xls]worksheet'!D11)"

thanks,

it didnt work, but I dont think your wrong. I've currently got it returning the value I want it to, but at the moment I've manually inserted 2008-10-21.

However, now whenever I make a change to that code it doesnt return the desired value. Is this Excel just playing up? Do I need to download an update?

arthurbr,

it now returns ('http://website.com/PMO/Meetings/39742[Spreadsheet.xls]worksheet'!D11)"

Why is it returning 39742 instead of 2008-10-21?

Cheers,
Alex

hehehe

2008-10-21 is 39742 - Excel stores dates as numbers, that's how it's able to add and subtract them. Try something along the lines of: text(<date cell>,"yyyy-mm-dd")

NB check whether your dates are mm-dd (2008-05-08) or m-d (2008-5-8)

HTH

thanks,

the link now looks correct in that its now showing:

('http://website.com/PMO/Meetings/2008-10-21/[Spreadsheet.xls]worksheet'!D11)

However instead of returning the value, it now just displays the above link as text.

Any ideas?

The value in the cell is ="('http://website.com/PMO/Meetings/"&X34&"/[Spreadsheet.xls]worksheet'!D11)"

Oh yeah, editing a hyperlink edits the "text to display" not the link itself. The thought did cross my mind...

I think we need VBA to live-edit hyperlinks - are you comfortable with this?

Please Login or Register  to view this content.

Put this code in the worksheet and try it

Charlie,

I think its almost there.

Could you tell me how we add 4 days to target?
Basically Target = Target + 4days

thanks

cheers mate,

I figured the plus 4 days out actually.

At the moment this returns the link itself to cell A1, but how do I actually return the value that the link is pointing too.

I think I dont actually need to create and add the link, do I just need to write something along the lines of:

A1 = firstpart & Format(Target + 4, "yyyy-mm-dd") & secondpart

??????

cheers cheeky charlie!

oh, I see.

Entirely different thing...

For this you just need to encase arthubr's solutions in indirect():
=indirect("('http://website.com/PMO/Meetings/"&W34&"/[Spreadsheet.xls]worksheet'!D11)")

Should have been a two post fix!

Still, interesting sideshow we did there.

Super.

Thanks, it works fine