Excel 03 VLOOKUP hyperlink referencing and formatting

[Brackes]

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.

 

[Brackes]

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]

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")

 

[Lost]

Is there a way to get it to display the friendly name and have it work?