Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 03 VLOOKUP hyperlink referencing and formatting
Hi all, my questions are at the bottom and here is an explanation.
I have a worksheet that contains street intersections and other important information about them. Column A has the intersections unique identifier number and E is the common name for each intersection. Each cell of E also has an associated hyperlink path to a .pdf file on our network that has pictures, plans, ect. This hyperlink is hidden though and can only be seen when holding the cursor over the cell. There are a few hundred intersections controlled by 5 engineers. What I want to do is take each engineers worksheet (tab) of intersections and populate a master list of all intersections on one worksheet so everyone can see the information and fallow the hyperlinks. A majority of the information in each engineers worksheet is updated/changed often so its important that a continuous referencing or updating to the master sheet occurs. Secondary need is to have each engineers worksheet (tab) protected that only they can edit it. I am using a Vlookup formula to reference each engineers information to a master sheet, but I cant get the imbedded hyperlinks of column E to reference. They just appear as simple text. Vlookup is looking for the unique identifier number of each intersection is column A of an engineers tab, Greg only and then populates the main sheet with each found row. =VLOOKUP($A12,'Greg only'!$A$3:$AE$120,E$1,FALSE) I can use =Hyperlink(*above formula*) but all this does is turns every cell into a visible hyperlink with a path of its own cell text. 1. is vlookup able to source the imbedded hyperlink to the master sheet? 2. vlookup produces 0 on the master sheet where there are blank cells on the source tabs. Can I prevent this from happening? It clutters the worksheet. 3. can vlookup produce formatting from the source sheets to the master sheet? i.e. in the source sheets I have columns that have conditional formatting to highlight them if they are overdue for a review. So in the source sheets they are red, but when vlookup produces these columns to the master sheet there is no highlighted formatting. If not, how could I add my conditional formatting formula to the above vlookup formula in the cells of the master sheet? Is it as simple as adding new conditional formatting to the columns of the master but reference the formulas on the source sheets? I cant seem to get it to work. Thanks for any help. I know its long but I wanted to get all the info down for best results. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 03 VLOOKUP hyperlink referencing and formatting
1. If you have a hyperlink of the form
=HYPERLINK(VLOOKUP()) then the VLOOKUP() must return an executable hyperlink, otherwise the cell will look O.K. but will fail on click. 2. If you don't like the 0, then: =IF(VLOOKUP()=0,"",VLOOKUP()) 3. VLOOKUP() can not "bring back" the formatting of the cell to which it refers. -- Gary''s Student - gsnu200827 "Brackes" wrote: Hi all, my questions are at the bottom and here is an explanation. I have a worksheet that contains street intersections and other important information about them. Column A has the intersections unique identifier number and E is the common name for each intersection. Each cell of E also has an associated hyperlink path to a .pdf file on our network that has pictures, plans, ect. This hyperlink is hidden though and can only be seen when holding the cursor over the cell. There are a few hundred intersections controlled by 5 engineers. What I want to do is take each engineers worksheet (tab) of intersections and populate a master list of all intersections on one worksheet so everyone can see the information and fallow the hyperlinks. A majority of the information in each engineers worksheet is updated/changed often so its important that a continuous referencing or updating to the master sheet occurs. Secondary need is to have each engineers worksheet (tab) protected that only they can edit it. I am using a Vlookup formula to reference each engineers information to a master sheet, but I cant get the imbedded hyperlinks of column E to reference. They just appear as simple text. Vlookup is looking for the unique identifier number of each intersection is column A of an engineers tab, Greg only and then populates the main sheet with each found row. =VLOOKUP($A12,'Greg only'!$A$3:$AE$120,E$1,FALSE) I can use =Hyperlink(*above formula*) but all this does is turns every cell into a visible hyperlink with a path of its own cell text. 1. is vlookup able to source the imbedded hyperlink to the master sheet? 2. vlookup produces 0 on the master sheet where there are blank cells on the source tabs. Can I prevent this from happening? It clutters the worksheet. 3. can vlookup produce formatting from the source sheets to the master sheet? i.e. in the source sheets I have columns that have conditional formatting to highlight them if they are overdue for a review. So in the source sheets they are red, but when vlookup produces these columns to the master sheet there is no highlighted formatting. If not, how could I add my conditional formatting formula to the above vlookup formula in the cells of the master sheet? Is it as simple as adding new conditional formatting to the columns of the master but reference the formulas on the source sheets? I cant seem to get it to work. Thanks for any help. I know its long but I wanted to get all the info down for best results. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 03 VLOOKUP hyperlink referencing and formatting
Gary,
thanks bad news though about the formatting, but i can work around that. Getting rid of the "0" worked perfect. it made the formula twice as long but i guess that makes me look twice as cool. as for the hyperlinking: my source cell has propper text, "8th St SW". i then right clicked on the cell and inserted a hyperlink by browsing the path. the =HYPERLINK(VLOOKUP()) you supplied only returns the propper name from the source cell as a selectable link via the path of its name "8th ST SW". i assume this falls into the catagory of "bring back", as in formatting. thanks for all the help "Gary''s Student" wrote: 1. If you have a hyperlink of the form =HYPERLINK(VLOOKUP()) then the VLOOKUP() must return an executable hyperlink, otherwise the cell will look O.K. but will fail on click. 2. If you don't like the 0, then: =IF(VLOOKUP()=0,"",VLOOKUP()) 3. VLOOKUP() can not "bring back" the formatting of the cell to which it refers. -- Gary''s Student - gsnu200827 "Brackes" wrote: Hi all, my questions are at the bottom and here is an explanation. I have a worksheet that contains street intersections and other important information about them. Column A has the intersections unique identifier number and E is the common name for each intersection. Each cell of E also has an associated hyperlink path to a .pdf file on our network that has pictures, plans, ect. This hyperlink is hidden though and can only be seen when holding the cursor over the cell. There are a few hundred intersections controlled by 5 engineers. What I want to do is take each engineers worksheet (tab) of intersections and populate a master list of all intersections on one worksheet so everyone can see the information and fallow the hyperlinks. A majority of the information in each engineers worksheet is updated/changed often so its important that a continuous referencing or updating to the master sheet occurs. Secondary need is to have each engineers worksheet (tab) protected that only they can edit it. I am using a Vlookup formula to reference each engineers information to a master sheet, but I cant get the imbedded hyperlinks of column E to reference. They just appear as simple text. Vlookup is looking for the unique identifier number of each intersection is column A of an engineers tab, Greg only and then populates the main sheet with each found row. =VLOOKUP($A12,'Greg only'!$A$3:$AE$120,E$1,FALSE) I can use =Hyperlink(*above formula*) but all this does is turns every cell into a visible hyperlink with a path of its own cell text. 1. is vlookup able to source the imbedded hyperlink to the master sheet? 2. vlookup produces 0 on the master sheet where there are blank cells on the source tabs. Can I prevent this from happening? It clutters the worksheet. 3. can vlookup produce formatting from the source sheets to the master sheet? i.e. in the source sheets I have columns that have conditional formatting to highlight them if they are overdue for a review. So in the source sheets they are red, but when vlookup produces these columns to the master sheet there is no highlighted formatting. If not, how could I add my conditional formatting formula to the above vlookup formula in the cells of the master sheet? Is it as simple as adding new conditional formatting to the columns of the master but reference the formulas on the source sheets? I cant seem to get it to work. Thanks for any help. I know its long but I wanted to get all the info down for best results. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 03 VLOOKUP hyperlink referencing and formatting
You just need to fix the table that VLOOKUP() examines.
Say a typical cell in the look-up table has either an Inserted hyperlink or something like: =HYPERLINK("http://www.cnn.com", "the news") The cell displays "the news" and is clickable VLOOKUP retrieves "the news" This is useless to HYPERLINK() because it wants the real URL, not the "friendly name" This means that the hyperlink in the look-up table must have the same "friendly name" as URL: =HYPERLINK("http://www.cnn.com","http://www.cnn.com") -- Gary''s Student - gsnu200827 "Brackes" wrote: Gary, thanks bad news though about the formatting, but i can work around that. Getting rid of the "0" worked perfect. it made the formula twice as long but i guess that makes me look twice as cool. as for the hyperlinking: my source cell has propper text, "8th St SW". i then right clicked on the cell and inserted a hyperlink by browsing the path. the =HYPERLINK(VLOOKUP()) you supplied only returns the propper name from the source cell as a selectable link via the path of its name "8th ST SW". i assume this falls into the catagory of "bring back", as in formatting. thanks for all the help "Gary''s Student" wrote: 1. If you have a hyperlink of the form =HYPERLINK(VLOOKUP()) then the VLOOKUP() must return an executable hyperlink, otherwise the cell will look O.K. but will fail on click. 2. If you don't like the 0, then: =IF(VLOOKUP()=0,"",VLOOKUP()) 3. VLOOKUP() can not "bring back" the formatting of the cell to which it refers. -- Gary''s Student - gsnu200827 "Brackes" wrote: Hi all, my questions are at the bottom and here is an explanation. I have a worksheet that contains street intersections and other important information about them. Column A has the intersections unique identifier number and E is the common name for each intersection. Each cell of E also has an associated hyperlink path to a .pdf file on our network that has pictures, plans, ect. This hyperlink is hidden though and can only be seen when holding the cursor over the cell. There are a few hundred intersections controlled by 5 engineers. What I want to do is take each engineers worksheet (tab) of intersections and populate a master list of all intersections on one worksheet so everyone can see the information and fallow the hyperlinks. A majority of the information in each engineers worksheet is updated/changed often so its important that a continuous referencing or updating to the master sheet occurs. Secondary need is to have each engineers worksheet (tab) protected that only they can edit it. I am using a Vlookup formula to reference each engineers information to a master sheet, but I cant get the imbedded hyperlinks of column E to reference. They just appear as simple text. Vlookup is looking for the unique identifier number of each intersection is column A of an engineers tab, Greg only and then populates the main sheet with each found row. =VLOOKUP($A12,'Greg only'!$A$3:$AE$120,E$1,FALSE) I can use =Hyperlink(*above formula*) but all this does is turns every cell into a visible hyperlink with a path of its own cell text. 1. is vlookup able to source the imbedded hyperlink to the master sheet? 2. vlookup produces 0 on the master sheet where there are blank cells on the source tabs. Can I prevent this from happening? It clutters the worksheet. 3. can vlookup produce formatting from the source sheets to the master sheet? i.e. in the source sheets I have columns that have conditional formatting to highlight them if they are overdue for a review. So in the source sheets they are red, but when vlookup produces these columns to the master sheet there is no highlighted formatting. If not, how could I add my conditional formatting formula to the above vlookup formula in the cells of the master sheet? Is it as simple as adding new conditional formatting to the columns of the master but reference the formulas on the source sheets? I cant seem to get it to work. Thanks for any help. I know its long but I wanted to get all the info down for best results. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 03 VLOOKUP hyperlink referencing and formatting
Is there a way to get it to display the friendly name and have it work?
"Gary''s Student" wrote: You just need to fix the table that VLOOKUP() examines. Say a typical cell in the look-up table has either an Inserted hyperlink or something like: =HYPERLINK("http://www.cnn.com", "the news") The cell displays "the news" and is clickable VLOOKUP retrieves "the news" This is useless to HYPERLINK() because it wants the real URL, not the "friendly name" This means that the hyperlink in the look-up table must have the same "friendly name" as URL: =HYPERLINK("http://www.cnn.com","http://www.cnn.com") -- Gary''s Student - gsnu200827 "Brackes" wrote: Gary, thanks bad news though about the formatting, but i can work around that. Getting rid of the "0" worked perfect. it made the formula twice as long but i guess that makes me look twice as cool. as for the hyperlinking: my source cell has propper text, "8th St SW". i then right clicked on the cell and inserted a hyperlink by browsing the path. the =HYPERLINK(VLOOKUP()) you supplied only returns the propper name from the source cell as a selectable link via the path of its name "8th ST SW". i assume this falls into the catagory of "bring back", as in formatting. thanks for all the help "Gary''s Student" wrote: 1. If you have a hyperlink of the form =HYPERLINK(VLOOKUP()) then the VLOOKUP() must return an executable hyperlink, otherwise the cell will look O.K. but will fail on click. 2. If you don't like the 0, then: =IF(VLOOKUP()=0,"",VLOOKUP()) 3. VLOOKUP() can not "bring back" the formatting of the cell to which it refers. -- Gary''s Student - gsnu200827 "Brackes" wrote: Hi all, my questions are at the bottom and here is an explanation. I have a worksheet that contains street intersections and other important information about them. Column A has the intersections unique identifier number and E is the common name for each intersection. Each cell of E also has an associated hyperlink path to a .pdf file on our network that has pictures, plans, ect. This hyperlink is hidden though and can only be seen when holding the cursor over the cell. There are a few hundred intersections controlled by 5 engineers. What I want to do is take each engineers worksheet (tab) of intersections and populate a master list of all intersections on one worksheet so everyone can see the information and fallow the hyperlinks. A majority of the information in each engineers worksheet is updated/changed often so its important that a continuous referencing or updating to the master sheet occurs. Secondary need is to have each engineers worksheet (tab) protected that only they can edit it. I am using a Vlookup formula to reference each engineers information to a master sheet, but I cant get the imbedded hyperlinks of column E to reference. They just appear as simple text. Vlookup is looking for the unique identifier number of each intersection is column A of an engineers tab, Greg only and then populates the main sheet with each found row. =VLOOKUP($A12,'Greg only'!$A$3:$AE$120,E$1,FALSE) I can use =Hyperlink(*above formula*) but all this does is turns every cell into a visible hyperlink with a path of its own cell text. 1. is vlookup able to source the imbedded hyperlink to the master sheet? 2. vlookup produces 0 on the master sheet where there are blank cells on the source tabs. Can I prevent this from happening? It clutters the worksheet. 3. can vlookup produce formatting from the source sheets to the master sheet? i.e. in the source sheets I have columns that have conditional formatting to highlight them if they are overdue for a review. So in the source sheets they are red, but when vlookup produces these columns to the master sheet there is no highlighted formatting. If not, how could I add my conditional formatting formula to the above vlookup formula in the cells of the master sheet? Is it as simple as adding new conditional formatting to the columns of the master but reference the formulas on the source sheets? I cant seem to get it to work. Thanks for any help. I know its long but I wanted to get all the info down for best results. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 03 VLOOKUP hyperlink referencing | Excel Discussion (Misc queries) | |||
Variable in VLOOKUP and referencing a cell another file in excel | Excel Worksheet Functions | |||
HYPERLINK IF referencing the cell of the function.. | Excel Worksheet Functions | |||
Hyperlink referencing | Excel Worksheet Functions | |||
vlookup function in excel referencing an access table | Excel Worksheet Functions |