Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using a cell reference as part of a link
For example, if the tab of the link is 2-5, and the date in column A is
2/5/06, we want to link to ='[book2]2-5'!. Where 2-5 also corresponds to =month(A1)&"-"&day(A1), therefore changing the link to the tab associated with the date in A1, then A2, etc. Can we use a "formula" (month, day, etc.), in a link? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using a cell reference as part of a link
Maybe...
If you use =hyperlink() in an adjacent cell. =HYPERLINK("C:\my documents\excel\book2.xls#"&"'"&TEXT(A1,"m-d")&"'!A1") Missy wrote: For example, if the tab of the link is 2-5, and the date in column A is 2/5/06, we want to link to ='[book2]2-5'!. Where 2-5 also corresponds to =month(A1)&"-"&day(A1), therefore changing the link to the tab associated with the date in A1, then A2, etc. Can we use a "formula" (month, day, etc.), in a link? -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using a cell reference as part of a link
Dave, i'm not looking for a Hyperlink so much as a linked reference to
another wkbk as source. Such as, when the source is updated or populated, this formula automatically updates as a result. I've been able to get the "link" to work as text, but now just to get the syntax correct as a formula. Here's my text: "[jeff_link_test.xls]")&Tab&("!$D$1"). Everything between the parenth. and quotes is actually a reference to another wkbk. The TAB name is a formula of month(c3)&"-"&day(c3), pertaining to the tab for each day of the month in the source wkbk. This part of the equation needs to change for each day listed in a column (c) in the non-source wkbk so, ideally, i can drag and drop the formula and the tab reference is a variable based on the date in column (C). -Missy "Dave Peterson" wrote: Maybe... If you use =hyperlink() in an adjacent cell. =HYPERLINK("C:\my documents\excel\book2.xls#"&"'"&TEXT(A1,"m-d")&"'!A1") Missy wrote: For example, if the tab of the link is 2-5, and the date in column A is 2/5/06, we want to link to ='[book2]2-5'!. Where 2-5 also corresponds to =month(A1)&"-"&day(A1), therefore changing the link to the tab associated with the date in A1, then A2, etc. Can we use a "formula" (month, day, etc.), in a link? -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using a cell reference as part of a link
You'd want to use =indirect() and build a string that looks like that reference.
But =indirect() won't work with closed workbooks. But... Harlan Grove wrote a UDF called PULL that will retrieve the value from a closed workbook. You can find the function at Harlan's FTP site: ftp://members.aol.com/hrlngrv/ Look for pull.zip Missy wrote: Dave, i'm not looking for a Hyperlink so much as a linked reference to another wkbk as source. Such as, when the source is updated or populated, this formula automatically updates as a result. I've been able to get the "link" to work as text, but now just to get the syntax correct as a formula. Here's my text: "[jeff_link_test.xls]")&Tab&("!$D$1"). Everything between the parenth. and quotes is actually a reference to another wkbk. The TAB name is a formula of month(c3)&"-"&day(c3), pertaining to the tab for each day of the month in the source wkbk. This part of the equation needs to change for each day listed in a column (c) in the non-source wkbk so, ideally, i can drag and drop the formula and the tab reference is a variable based on the date in column (C). -Missy "Dave Peterson" wrote: Maybe... If you use =hyperlink() in an adjacent cell. =HYPERLINK("C:\my documents\excel\book2.xls#"&"'"&TEXT(A1,"m-d")&"'!A1") Missy wrote: For example, if the tab of the link is 2-5, and the date in column A is 2/5/06, we want to link to ='[book2]2-5'!. Where 2-5 also corresponds to =month(A1)&"-"&day(A1), therefore changing the link to the tab associated with the date in A1, then A2, etc. Can we use a "formula" (month, day, etc.), in a link? -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Q: Can a formula reference a cell to get the file name to link to for data? | Excel Discussion (Misc queries) | |||
Input cell reference is not valid (One Variable Data Table) | Excel Worksheet Functions | |||
Clicking Cell Link Changes Cell on Another Sheet | Excel Discussion (Misc queries) | |||
Use Cell Content to Build Link | Excel Worksheet Functions | |||
worksheet tab name as part of a cell reference | Excel Worksheet Functions |