Forum Discussion

Johnny Kanehl's avatar
Johnny Kanehl
Copper Contributor
Nov 28, 2017
Solved

Dynamic hyperlink using cell data

Hello,

 

I'm trying to create an invoice/timesheet template that links directly to my paypal.  The total invoice amount is in cell L3, and is derived from a formula adding all the hours and multiplying by hourly rate.  In K4 is a link to pay the exact amount via paypal using this formula:

 

=HYPERLINK("https://www.paypal.me/MyUserName/"&L3)

 

The output in L3 is set to display two decimal places, so in this case it displays exactly 102.50, but when cell K4 draws the information it looses the two decimal place formatting and directs the hyperlink to https://www.paypal.me/MyUserName/102.5 which doesn't go anywhere. I need the link to be exactly the same as L3 so it goes to https://www.paypal.me/MyUserName/102.50 

 

Does anyone know how to force cell K4 to use the two decimal formatting when inserting the amount into the URL?

 

Thank you!

Formulas & Functions
Need Help
  • Damien_Rosario's avatar
    Damien_Rosario
    Nov 28, 2017

    Hi Johnny

     

    Give this workaround a try?

     

    1. Create an empty column to the right of your dollar values (e.g. Your $ value is in L3, to the empty column should be M). Note you can hide this column later.
    2. In the M cell (e.g. M3), type in the following formula =TEXT(L3,"#,##0.00").
    3. Make sure that your hyperlink formula =HYPERLINK("https://www.paypal.me/MyUserName/"&M3) refers to column M (the modified text formula cell).
    4. Your text in your hyperlink formula should now have a persistent two decimal places no matter what you two decimal placed value you enter in.

    I've attached the sample file I have made on your scenario (and sample image below).

     

    See the attached file for the full version

    See if that helps?

     

    Cheers

    Damien

3 Replies

  • Damien_Rosario's avatar
    Damien_Rosario
    Silver Contributor
    Nov 28, 2017

    Hi Johnny

     

    Give this workaround a try?

     

    1. Create an empty column to the right of your dollar values (e.g. Your $ value is in L3, to the empty column should be M). Note you can hide this column later.
    2. In the M cell (e.g. M3), type in the following formula =TEXT(L3,"#,##0.00").
    3. Make sure that your hyperlink formula =HYPERLINK("https://www.paypal.me/MyUserName/"&M3) refers to column M (the modified text formula cell).
    4. Your text in your hyperlink formula should now have a persistent two decimal places no matter what you two decimal placed value you enter in.

    I've attached the sample file I have made on your scenario (and sample image below).

     

    See the attached file for the full version

    See if that helps?

     

    Cheers

    Damien

Resources