JylanDames
New Member
- Joined
- Mar 18, 2021
- Messages
- 1
Hello guys! Firstly, feel free to save time and jump straight to the TLDR bold section below, then read the rest of this long post only if more context is needed to help. This is probably a super easy problem for most of you, so trying to respect your time here, but also include context incase more info is needed! I have been searching this forum, Google and everything in between but trying and failing, so I'm making this post hoping I can get some help (also hoping that I didn't miss a thread and am repeating questions here, haha, I am sorry if so)!
Context on what I'm trying to do. At work, we're pulling a set of data from Microsoft SQL. The data is of tickets assigned to us, but were closed by someone other than us. We paste that into a sheet (full disclosure, it is Google Sheets, but either will work for this!), and then we have to copy each ticket number manually and paste it into our web-based system to be able to search and review the ticket. Obviously, that is not optimal for efficiency; it makes it extremely hard to get through 90+ tickets in 2 hours.
Problem: I am trying to design a hidden column that will pull the ticket number from the cell to the left and insert it into the right spot in the URL for our system, then have the ticket number hyperlinked to that created hidden URL (or if not the ticket number, even have a phrase in a 3rd, unrelated cell hyperlink to the URL -- just something clean to click on) so that we can just click the ticket number from the sheet to automatically open the ticket in a webpage.
What I have so far is a formula that will pull the ticket number from the desired column/row (I.E. B3, B4, etc.) and insert it in the right place in our URL, creating a working URL that can be clicked to view that ticket. Not really a formula, just the desired URL with the cell number where the ticket # needs to be inserted. Looks like this (the Xs and XYZs are to protect the company name and info): ="https://XXX.COMPANYNAME.com/v/XXXXX_XXXX_XYZ_ticket/customer_service_XYZ_ticket/index?ticketId="&B3
TLDR - Where I am stuck: I have been unable to find a way to make a hyperlink for the ticket number column contain the URL in the column to the right. For example, it looks like this: Column B contains the ticket number, column C contains the URL that pulls the ticket number from column B. All I need to do now is make the value(s) in column B hyperlinked to the URL that is created in column C, again, to be able to have the ticket number link directly to its unique webpage -- I hope that makes sense. Or, like I said above, I don't mind if B contains the ticket number, C contains the URL pulling the value from B, then I make a new phrase, such as 'Click Here', in column D that is hyperlinked to the URL from C.
It works just fine to have a column with a big URL in it for personal use, it does the job; it works. I can click the link to go straight to the webpage of the target ticket and solve the problem of having to copy/paste each ticket number. But I am creating a template for my team to be able to copy the sheet and paste their tickets in and they're all hyperlinked and ready to go. So, I need to clean it up and ensure that it is as fool proof and plug and play as possible.
I realize this is probably simple as can be for any experienced user. I have searched every where trying to find something, that's how I've gotten this far, because I wanted to do my do-diligence before I bothered people with a post. I am teaching myself Excel/Sheets, and this has caused me a roadblock, so any help is greatly appreciated! This forum is like my go-to when I am teaching myself something, so I thought this would be the place to go!
Thank you in advance!
-A grateful noob.
Context on what I'm trying to do. At work, we're pulling a set of data from Microsoft SQL. The data is of tickets assigned to us, but were closed by someone other than us. We paste that into a sheet (full disclosure, it is Google Sheets, but either will work for this!), and then we have to copy each ticket number manually and paste it into our web-based system to be able to search and review the ticket. Obviously, that is not optimal for efficiency; it makes it extremely hard to get through 90+ tickets in 2 hours.
Problem: I am trying to design a hidden column that will pull the ticket number from the cell to the left and insert it into the right spot in the URL for our system, then have the ticket number hyperlinked to that created hidden URL (or if not the ticket number, even have a phrase in a 3rd, unrelated cell hyperlink to the URL -- just something clean to click on) so that we can just click the ticket number from the sheet to automatically open the ticket in a webpage.
What I have so far is a formula that will pull the ticket number from the desired column/row (I.E. B3, B4, etc.) and insert it in the right place in our URL, creating a working URL that can be clicked to view that ticket. Not really a formula, just the desired URL with the cell number where the ticket # needs to be inserted. Looks like this (the Xs and XYZs are to protect the company name and info): ="https://XXX.COMPANYNAME.com/v/XXXXX_XXXX_XYZ_ticket/customer_service_XYZ_ticket/index?ticketId="&B3
TLDR - Where I am stuck: I have been unable to find a way to make a hyperlink for the ticket number column contain the URL in the column to the right. For example, it looks like this: Column B contains the ticket number, column C contains the URL that pulls the ticket number from column B. All I need to do now is make the value(s) in column B hyperlinked to the URL that is created in column C, again, to be able to have the ticket number link directly to its unique webpage -- I hope that makes sense. Or, like I said above, I don't mind if B contains the ticket number, C contains the URL pulling the value from B, then I make a new phrase, such as 'Click Here', in column D that is hyperlinked to the URL from C.
It works just fine to have a column with a big URL in it for personal use, it does the job; it works. I can click the link to go straight to the webpage of the target ticket and solve the problem of having to copy/paste each ticket number. But I am creating a template for my team to be able to copy the sheet and paste their tickets in and they're all hyperlinked and ready to go. So, I need to clean it up and ensure that it is as fool proof and plug and play as possible.
I realize this is probably simple as can be for any experienced user. I have searched every where trying to find something, that's how I've gotten this far, because I wanted to do my do-diligence before I bothered people with a post. I am teaching myself Excel/Sheets, and this has caused me a roadblock, so any help is greatly appreciated! This forum is like my go-to when I am teaching myself something, so I thought this would be the place to go!
Thank you in advance!
-A grateful noob.
