As was foretold, we've added advertisements to the forums! If you have questions, or if you encounter any bugs, please visit this thread: https://forums.penny-arcade.com/discussion/240191/forum-advertisement-faq-and-reports-thread/

Quick Excel question for Excel Pros

FireflashFireflash Montreal, QCRegistered User regular
edited November 2011 in Help / Advice Forum
Hello!
I have the impression that what I want to do in my document isn't possible but I wanna ask around just in case there's some mysterious way to do this.

Without going in details, here's my setup and what I need: There's a database online, with numbered entries. Each entry has it's own webpage. I have a shared excel document in which people will sometimes need to add an numbered entry from the online database. People need to be able to click on that number in the excel document to go directly to that entry in the online database.

What I know I can do: The typical setup for this is using 2 columns. For example the user types the entry # in column F, and there's a formula in column G that uses the number from column F to create an hyperlink directly to that entry in the database. So in the document you see the entry numbers in column F and the hyperlink to that entry in column G. This works fine but it makes an extra "useless column".

What I would like: Have a formula that for example if I type an entry number in column E will create the appropriate hyperlink in the very same cell I typed the number in. Can it be done?


Thanks!

PSN: PatParadize
Battle.net: Fireflash#1425
Steam Friend code: 45386507
Fireflash on

Posts

  • schussschuss Registered User regular
    Where A1 is the cell where someone typed a number:
    ="Hyperlink text"&A1&"any other hyperlink ending"

    I think that should do it.

  • KafkaAUKafkaAU Western AustraliaRegistered User regular
    To do it in the same cell you type the number in you would need to write some VB code which would create it for you after you type the number in.

    steam_sig.png
    Origin: KafkaAU B-Net: Kafka#1778
  • SpongeCakeSpongeCake Registered User regular
    It's not possible to do it in Excel without using VBA or forms (as whatever you type in the cell will overwrite whatever code you'd put in there to do the thing you want to do!)

    You could create a simple form which would prompt the user to enter a number and then trigger your hyperlink. Not too complicated if you have experience with forms. You could also write some VBA which would trigger on cell change.

  • tastydonutstastydonuts Registered User regular
    edited November 2011
    What you want to do doesn't seem practical, imo. But this macro can do what you want. o_o

    Worksheet_Change is the event for when a worksheet, well... changes.

    The steps are as follows:

    1) Grab the existing value from the origin cell.
    2) Generate an url from that value.
    3) Return/change the origin cell's value to the url.
    Private Sub Worksheet_Change(ByVal Target As Range)
    
      ' Is the change in row E? (Or any other row. You can specify a cell by saying Target.Row <> X OR Target.Column <> Y)
      ' And so forth.
      
      If Target.Column <> 5 Then
        Exit Sub
      End If
      
      On Error GoTo ErrHandler
    
      ' Disable events temporarily otherwise the value it will put into the
      ' cell's link is the text you describe the link as...
      
       Application.EnableEvents = False
      
      ' Store what is in there.
      
      Dim tempval As String
          
      tempval = Target.Value
      
      If tempval <> "" Then
      
        Target.Value = "=hyperlink(""http://www.destination.com/myurl.php?databaseID=" & tempval & """,""Link Description!"")"
    
      Else
      
        Target.Value = tempval
      
      End If
        
    
      ' Turn events back on.
      Application.EnableEvents = True
      Exit Sub
    
    ErrHandler:
     
      Application.EnableEvents = True
      Exit Sub
        
        
    
    End Sub
    

    Edit: There shouldn't be a semicolon on the link line ("Target.Value..." ) and there isn't when I edit it, mind that.

    Edit2: You Need the multiple "s in order to escape the "s in the formula text, too.

    edit3: Fixed it so you can actually delete the contents of a cell.

    tastydonuts on
    “I used to draw, hard to admit that I used to draw...”
  • FireflashFireflash Montreal, QCRegistered User regular
    edited November 2011
    Thanks! I'm not used to using macros much but I'll see what I can do with this!

    Dunno why it doesn't seem practical to you though :P

    People make some tests/verifications on some things listed in the excel document. If there's an issue they need to create that issue in the database and link it in the excel document. Having them only type the number of the issue to have an hyperlink makes it simple and fast for them. Especially since adding a hyperlink manually in a shared document doesn't seem to work very well. Much faster to just type the number in a cell.

    It's a bit nit picky but having the issue number in a column and the link to said issue in a different one is just not as clean :P

    Fireflash on
    PSN: PatParadize
    Battle.net: Fireflash#1425
    Steam Friend code: 45386507
  • tastydonutstastydonuts Registered User regular
    edited November 2011
    Oh, it's mostly because many users have macros disabled by default so the document won't work properly vs having the 2 columns. Or Excel warns them... then you have people that go "Why does Excel keep asking me about macros?" If everybody using it has em on and all, you're good. but otherwise... :P

    edit: Make sure you check that edit. You couldn't just clear the contents of a cell without the event filling in a bad link. I fixed it so it'd let you do that now.

    tastydonuts on
    “I used to draw, hard to admit that I used to draw...”
Sign In or Register to comment.