Need Help With Excel Formula - Hyperlinks and If Statements

[crashhelmet]

I'm trying to add a link into an excel worksheet that pulls values from the cells to provide the information for the hyperlink to a ticket in one of our databases.

I had it working at one point, but it was opening the ticket instead of "viewing" it. When i tried to correct it, it stopped working again.

They hyperlink to view the ticket should look something like this

hxxps://companyserver/tickettype_q.asp?qid=####&tick=####&redirect=qman. asp%3Fqid%ABCD&viewonly=1

A1 has the ticket number (tick####)
B1 has the qid. This is where the If statement comes in. If the text in B1 says "Open" then the qid would be 1000, if it's pending update the qid would be 1010, etc. There are a total of 7 different QIDs

My equation currently looks like (With necessary corrections for security purposes)

=HYPERLINK("hxxps://servername/tickettype_q.asp?qid="&IF(B1="Open",1000,IF(B1="Pe nding Information",1100,IF(B1="Pending Update",1010,0)&"&tick=#value(A1)&redirect=qman.as p%3Fqid%ABCD&viewonly=1

I still need to add the others, but I can't seem to get it to work with what I've got so far.

Any recommendations?

Thanks!

CH

[crashhelmet]

As a follow up, it appears that my portion of the formula to fill in the proper ticket number isn't working. It opens the oldest ticket in the specified QID instead of the one specified.

Hmm.....

CH

Edit... Not sure why I didn't edit the last post. Sorry about that. But here's another correction. it's not even pulling from the proper QID. It's opening the oldest ticket across all QIDs.

CH

[EvilEmpryss]

I'm going to see what I can do with this, but a little thing I noticed that I wanted to ask first: There are some irregular spacings in the formula you posted (e.g. B1="Pe nding). Are these intentional, a mistake in posting it here, or actually part of your formula?

If they're part of your formula, that may be your problem right there. That's probably too easy a fix, though, so I'll keep working.

ETA: Also, you don't seem to have enough close parentheses. Count your open parentheses, and then count the closers; they have to be the same amount.

[Berys]

Assuming that the oldest QID has the lowest number, that sounds like it might be a LOOKUP error somewhere along the line. The problem with a LOOKUP function is that if it doesn't find the value it is hunting for it will pick the one that is closest to it. Course this would also imply that somewhere along the line it is assuming that the ticket# is a really low value.

If a LOOKUP function isn't used anywhere, then please just ignore me.

Assuming the spaces in there are just typos, as well as the missing quotation marks and parenthesis.

[crashhelmet]

I'm going to see what I can do with this, but a little thing I noticed that I wanted to ask first: There are some irregular spacings in the formula you posted (e.g. B1="Pe nding). Are these intentional, a mistake in posting it here, or actually part of your formula?

If they're part of your formula, that may be your problem right there. That's probably too easy a fix, though, so I'll keep working.

ETA: Also, you don't seem to have enough close parentheses. Count your open parentheses, and then count the closers; they have to be the same amount.

That was a typo in my effort to change things. Good eye on the parentheses. I caught a problem with the section to pull the value for the ticket number too.

The formula now works, I'm only wondering if there is a way to simplify it. This is what I have.

=HYPERLINK("hxxps://servername/tickettype_q.asp?qid="&IF(B2="Open",1000,IF(B2="Pe nding Information",1001,IF(B2="Pending IT",1002,IF(B2="Pending Vendor",1003,IF(B2="Pending NOC",1004,IF(B2="Pending Root",1005,IF(B2="Pending RF",1006,0)))))))&"&tick="&A2&"&redirect=qman.asp% 3Fqid%ABCD&viewonly=1")

Thanks again for the help!

CH

[EvilEmpryss]

The biggest problem with Excel is that sometimes the only way to do something is the long way. I'm glad I could help.

When I took an Excel training class yeeeeeears ago, my instructor always said that the first thing to check in a non-functioning formula was the spacing and the parentheses. . . those and the quotation marks will screw up a formula faster than anything else. I loved it when MS changed things so the parentheses came up different colors... it made it easier to match them up in pairs.

[Rapscallion]

The spaces are likely the result of the fifty-character limit imposed by the board software to prevent (to some extent) stretched pages. Using the [code] tags could help.

Rapscallion