launching same hyperlink in dynamic cells

[Fairport]

I am designing a spreadsheet for engineering service faults. One of the requirements is to launch Google Maps to see where the customer is situated. I am able to write a formula eg =HYPERLINK("http://maps.google.com/maps?q="&G2&"+&iwloc=A&hl=en"), in a cell that looks up the postcode (in my attached example, cell s G2 and G3) and then launch the map by clicking the adjacent cells in H2 and H3. What I would like to do is, do away with the unsightly looking Hypertext in the cell and write code to do the following:- if a cell in column G is blank then the adjacent cell in H is also blank. If a post code is in a cell in G then “Map” appears in the cell next to it in H. Then when the user wants to look at where the customer is clicks on “Map” and Google Map is launched. The same hyperlink is used for all instances and the column will be the same but rows will be dynamic.
Many thanks in anticipation.

[sweep]

Hi,

In H2 and copied down:

=IF(ISBLANK(G2),"",HYPERLINK("http://maps.google.com/maps?q="&G2&"+&iwloc=A&hl=en","MAP"))

[Andy Pope]

Formula in H2 copied down.


=IF(G2="","",HYPERLINK("http://maps.google.com/maps?q="&G2&"+&iwloc=A&hl=en","Map"))

[Fairport]

Ddiolch filiwn. Although that doesnt translate directy to "Thanks a million" in Welsh the sentiments applies to both, thanks guys. The cell with the hypertext is now blank, a) it doesn't invoke the link and b) "Map" doesn't appear in the cell.
I will persist.
Would like to know how to do it in VBA if possible please?

[sweep]

Croeso - mae'n bleser!

[Fairport]

For those of you who require a tool to translate Welsh (or any other language) go to:-

Sorry guys both your formulas worked, in my excitement to get the thing working I forgot to change the cell references in the formulas in the actual spreadsheet! Its been a long hard week!!!
Although this solution will do the job adequately if anyone comes up with a VB solution I would still be interested, (learning curve and all that). Thanks again.
pawb ca da benwythnos

[Andy Pope]

Please Login or Register  to view this content.

[Fairport]

This looks interesting

Please Login or Register  to view this content.

Where do I paste the code? I've tried copying it to 'sheet1(Data)' in the original example I attached but nothing appears to be working. My lack of knowledge playing a big part again!!

[Andy Pope]

You could put it in a standard code module.
ALT+F11 (VBE)
Insert > Module

You would need to place it in subroutine

Please Login or Register  to view this content.

[Fairport]

Thank you. With a bit of tweeking here and there it works fine.