Link to convert pounds to kgs?

[leopardhawk]

Hello forum friends, I am wondering if it might be at all possible to have a worksheet with some text that was hyperlinked in such a way that clicking it would convert numerous cells (~45) containing numbers representing pounds to kilograms? Some cells are where the user actually enters data (in pounds) and there are other cells containing formulas that return results in pounds (if that makes a difference). Conversely, there would also be a way to convert them back to pounds.

Just wondering if there are any ideas out there about how this might be accomplished.

Thanks!

 

[jasonb75]

You could always use the CONVERT function in adjacent cells, far less complicated than trying to do it with hyperlinks.

 

[jpen]

Why not use a shape and assign a macro to it which doing the calculations.

 

[leopardhawk]

@jasonb75 thanks for the suggestion. The look and feel of the worksheet needs to stay the way it is, so I was hoping there was a simple way to 'click' something to switch the data in these cells over to the metric system and then click it again to switch back to the imperial system. Ford does it on their website, giving users the option to choose between the two systems when viewing specs for their vehicles. I just have no idea how to do it.

If it's too complicated, I can pass but I think it would be really beneficial for my target users to be able to choose which system worked best for them.

 

[leopardhawk]

@jpen thanks, not sure how to do that, newbie...

 

[mikerickson]

What you could do is put a Validation list in a cell, allowing the user to pick what units the rest of the spreadsheet is in.

The secret part of all of this is that you and your calculations are all in the background using the system that is most convenient for you.
The drop down and the resulting conversion to other units is for user interfacing only. The calculations and records are kept in your prefered system.

 

[leopardhawk]

@mikerickson excellent idea, I will play with that and update after. Thanks!

 

[leopardhawk]

@mikerickson after messing around a bit with this, I have decided it's too much effort for very little gain. Thanks to all who made suggestions.

 

[MARK858]

@leopardhawk, see the workbook in the link below. To run change the dropdown in C2.

Leopardhawk.xlsm | Powered by Box

app.box.com

Obviously you need to password protect the vba and hide/move the list in I1:I2.

The code in the worksheet module is

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Cells.Count = 1 And Target.Address = "$C$2" Then
        Application.EnableEvents = False

        If Target.Value = "Convert to kilograms" Then
            With Range("A2", Cells(Rows.Count, "A").End(xlUp))
                .Value = Application.Evaluate(.Address & "*" & 0.45359237)
            End With
        End If
   
        If Target.Value = "Convert to pounds" Then
            With Range("A2", Cells(Rows.Count, "A").End(xlUp))
                .Value = Application.Evaluate(.Address & "*" & 2.20462262)
            End With
        End If
 
        Application.EnableEvents = True
    End If
End Sub

 

[leopardhawk]

@MARK858 Thank you, I will give this solution a try. I was hoping not to have a macro-enabled workbook but maybe that's the only way to accomplish this.

I wonder what Ford is doing on their website to accomplish basically the same thing.

https://www.ford.ca/trucks/super-duty/models/f350-platinum/

Scroll down and click on 'Specifications', you will see their hypertext on the right side of the screen.