So I have a code that creates a refreshable list of sheet names in a TOC (I call it a "directory" in the code). Problem is it creates the ugly standard hyper link look. I can format the look of the cells, of course, but when the refresh button is hit, the formatting is erased and it all goes back to the standard hyperlink blue underlined look. How to keep the formatting permanent?
Here is the macro I am using - [Code] ..........
I have a workbook linked to an external source that is refreshed once a week. All the refreshed data is populated in column A. User defined text is typed into columns B,C,D and E. As the report increases or reduces in column A the text in B,C,D and E becomes out of sync with its original data in column A. Is there a way of keeping the data and tagging it somehow?
View 4 Replies View RelatedI have 5 pivot tables which feeds data from a single worksheet. The data from this sheet change every month. Meaning, old data out (deleted) and new one's inn.
The problem I have is the old data I used in the Row Label (such as Customer Name) are still showing in all the pivot tables even though the record is no longer there.
I am importing data from an access database that is continually refreshed. Some of the data that is returned to my Excel sheet is in binary, where a "0" represents an unchecked box on the database program and a "1" represents the same box, but with a check mark. I want to change the data so that when it is updated on my Excel sheet it is converted to text as either "Yes" or "No" instead of "1" or "0", respectively. I'm sure this is pretty elementary stuff but I have tried VBE and cell formulas and can't come up with an answer.
View 3 Replies View RelatedI've only recently began to use excel, but I've really dived deep into it, I'm clueless when it comes to VBA but now I'm stepping into that realm. Anyway, I'm using this code....
Code:
Private Sub Worksheet_Change(ByVal Target As Range)If Not Intersect(Target, Range("A:A")) Is Nothing Then
Application.EnableEvents = False
Me.Sort.SortFields.Clear
Me.Sort.SortFields.Add Key:=Range("A:A"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With Me.Sort
[Code].....
Code:
Credits to VOG
Link: auto-sort for excel 2010.... to auto sort 2 columns of data. However, it is extremely tedious to enter data as it sorts itself every time you enter a new data point. Moreover, using any random number generator to fill the columns takes ages! Is there anyway to make it so the code only runs when the worksheet is refreshed (F9 pressed.)
I have a spreadsheet and within the first sheet there are lots of hyperlinks to other cells within sheet 1 (my template).
I have made a small macro which very basically makes a copy of sheet 1 (my template)
The hyperlinks in the template are explicit and as they are copied to sheet two, they still reference back to cells in sheet 1. I need the hyperlinks to be relative, so that when I make a copy of the template the hyperlinks are copied and make reference to cells in the new sheet. I cannot work out how this should be done
I have the following vlookup formula in my spreadsheet.
IF(ISNA(VLOOKUP($A2,'Purchase Order Pivot Table'!$5:$500,67,FALSE)),0,VLOOKUP($A2,'Purchase Order Pivot Table'!$5:$500,67,FALSE))
It works perfectly for my current requirement, but it looks at data in a pivot table which is created from a dataset. If I name the columns in the pivot table so instead of looking at column 67 it looks at a name range will this name range change dynamically when the pivot table is refreshed? If not how can I get it to.
[URL]....
I have 4 workbooks, all of them contain data that is managed by other teams at my company. I need to take all of this data, organize, and concatenate it into one sheet for myself, that I will then reference in other workbooks to various OTHER departments in our company that need the data for certain projects. I have the organization laid out in a lot of very complex formulas so that most of this is automated, and not manual.
I need a solution that can pull data from the 4 workbooks without locking them for use, to allow me to keep my document open all the time, and they can update theirs at their leisure. Everything works fine, until I hit the refresh button. Once this is hit, all of the linked documents are locked and cannot be opened, even in "Read Only" mode. Once I close the master document, the files are free to be opened. I know a workaround for now is that i can not "refresh" the document, but rather close and re open it for changes.
Some steps I have already tried :
1. Create an intermediary file that no one uses, and can be locked all day without a problem. This doesn't work for me since the intermediary file needs to be open to refresh, which locks the original source doc, leaving me in the same place as before.
2.Modify the connection string to display "Mode=Read;" instead of "Mode=Share Deny Read"
3.Create a new connection with "Read" only selected in the Advanced Tab
how to protect a worksheet from editing and at the same time allow all data source links within the sheet to be refreshed? I need my users to be able to refresh all data links as required but not be able to change the worksheet in any other way.
View 2 Replies View RelatedI have a spreadsheet that when an identity code is keyed in, it will display a make and model of a particular mobile phone, i want to put a hyperlink in the cell next to the phone name which will then open up an IE window to a website.
The thing i want ideally is if there is any way you can incorporate the websites search function into the hyperlink so when you click on it, the hyperlink will copy the phone details over to the website's search engine and take you straight to the online details of the phone?
wow this isnt making sense to me!
For e.g
The identity code shows up a Nokia 1500
The hyperlink next to it takes in the manufacturer/model details "nokia 5300" (these will obviously vary each time so takes in a cell reference should i say)
It opens up the ie window e.g "google" and automatically searches for that "nokia 5300" and comes up straight away with the search results.
Is there a way of using something like hyperlinks in VBA code to open a user form?
I presently am using hyperlinks for coworkers to open other pages that resemble a user form.
I would like to change this to actually using user forms.
I would prefer to simply click on a person's name and open the form.
I need returning a working hyperlink from a vlookup. I have a table with part #'s and hyperlinks. On another tab I want to type in a part number, have it search the table and return the working hyperlink. This will allow the user to just click it and go to that tab.
Here is my example spreadsheet : Book1.xlsm
I created a data base that had a hyperlink to a plan of the store so we can find parts easy. With a lot of help for people here on this web site, it works very well.
My only problem now is that I sorted my Database by the hyperlink column. It made it easy to do one hyperlink and pull the fill handle down for the rest of the same hyperlink reference.
When I went to resort the database so that the parts were in alphabetical order. The sort worked well except that the hyperlink for lets say cell F87 was not sorted. Example : - F87 hyper link presort had the reference to Rack 10 self B. Post sort it had the reference to Rack 16 shelf C. But when clicked on it still took you to Rack 10 self B.
When I click on a hyperlink inside an Excel spreadsheet, it is linked to a PDF file. When clicked, the screen flashes the Adobe Acrobat program for a split second and then disappears. But I can open the hyperlink same as which it is linked to a word file in the same directory.
I have gone into folder options and switched the PDF to open with Adobe reader 8, but that does nothing.
Any ideas of how to get the PDF to open and stay open would be most appreciated.
I tried to install adobe reader 4.0 without uninstall adobe 7.0 professional.
I use pdf reader 4.0 open the Pdf file in the window explorer, and then go to excel, open the same hypelink pdf file, and find it works now only opening pdf document by pdf reader 4.0 but not by 7.0.
But when I resinstall adobe 7.0 professional, and the problem still occurs. I have to open the original pdf file in the directory by adobe reader 4.0 again.
My case is like the following Macro in excel.
Setup hyperlink and open it . Then the problem is shown as my previous thread mentioned.
Sub Review() .....
The following is a simplified example of my problem: I have a list of product item numbers in column A. Next to each item number in column B, is a hyperlink to a PDF file relevant to each item. Elsewhere in the sheet, is a drop down box containing the item numbers. Next to the drop down box is a HYPERLINK(VLOOKUP) that fetches the relevant hyperlink depending upon the selected item in the drop down box. Problem is, when I select the ‘lookup’ link, I get an error message ‘cannot open the specified file’ (the original Hyperlink works fine). Does anyone have a solution to this problem (preferably without resorting to Visual Basic?
View 13 Replies View RelatedI have a database of two columns - a file name and a hyperlink for the folder path to that form. What I would like to do is a VLOOKUP type search by the form name and the result be the hyperlink itself. The problem is that vlookup basically provides a "Values Only" response and doesn't carry over the hyperlink format.
View 2 Replies View RelatedI made the mistake of creating hyperlinks to files on a server (using the \SERVERNAME convention) without using the "hyperlink base" option.
I've been sufficiently chastened, but the present problem is that I have many such hyperlinks that are now worthless because, when saved, they point to "../../[foldername1]/[foldername2]/[filename]", etc.
Need macro that would replace the first five characters ("../..") of all the hyperlinks on my sheet with //[MYSERVERNAME] ?
how to use a combo box to select hyperlinks to take you off to the selected website? I have this, which works but it's hard-coded - I'm looking for something a little more dynamic, i.e. pick up hyperlinks from a range on a sheet.
View 2 Replies View RelatedCan a message box support a hyperlink? I prompt a user with a message box with the text to a website, can the text for the website be a hyperlink in that message?
View 2 Replies View RelatedHave the following dilemma in Excel: If I enter a value in cell A3 and the value corresponds to a reference value in one of cells B4:B12, can I hyperlink data in cell range C4: C8 to elsewhere on the sheet (eg J4: J8).
Example.
I have 8 meeting rooms that each have a dedicated code, AU, BR, FOR, TO, M1, M2, M3, M4
When I enter a code in cell A3, (eg AU) I want the data in cells A4: A8 (date, start, end, event) to be reproduced in another table on the sheet.
In other words: If A3 is equivalent to a value in cells B4: B12, then can data in C4:C8 be reproduced in J4: J8?
I'm trying to build a workbook that has a drop down box with 5 options. I want to be able to select an option out of the drop down menu, and have about 5 cells populate on that same sheet based on the what I select in the drop down box. I used Data Validation and List to make my drop down box, so that part is no problem. I can use an if statement to populate cells with names based on what I choose in the drop down box, so that's not really my problem either. The problem I have is that I was to make each name that populates based on what I select in the drop down box to be a hyperlink to a separate sheet within the same workbook.
I need these hyperlinks to go to different sheets based on what I choose from the drop down box. To tell you exactly what I'm doing: I have 5 locations. Each location has 3 to 5 supervisors. Each supervisors has 6 to 8 laborers under them. I want to have a drop down box with my location names. When I choose a location, I want to see the names of the supervisors at that location populate. I then want to be able to click on the name of the supervisor and have a hyperlink to a different page within that workbook to tell me data on the laborers under the supervisor..
Is there anyway in VBA, that if a hyperlink is clicked on in the spreadsheet that it won't follow the link?
I need it to be a link in every other respect because I will be parsing the Text to Display, and utilizing the links address. I just would like to not have the link be clickable on the spreadsheet.
I have a workbook with 32 sheets. The first 31 sheets are hyperlinked to the 32nd sheet, which holds the summary from the other 31 sheets. Now, if I am in Sheet7 and click on the hyperlink in sheet7, it will take me to the summary sheet. That part is OK. But I want to know is it possible to go back to the sheet from where I reached the summary sheet (in this case sheet7 itself) with hyperlinks.
View 9 Replies View RelatedI am working on a database for a client. The database has a few worksheets with an overview (contents) worksheet. What I am trying to do is create a hyperlink from the contents page to the corresponding worksheet. I can do this no problem...my problem is that if I add more data to the worksheet the hyperlink is lost. I am linking the sheets via cells so if a cell is moved down to add a new cell the hyperlink is no longer valid. Is there a rule I can create to keep the hyperlink and not have to adjust it every time new data is added.
View 9 Replies View Relatedi have a WB with over 28 sheets in is there any way of having a box wether it MsgBox or anything to give the user a choice of which sheet to goto , dont have room to add a list of hyperlinks , was wondering if this was possible atall
I have a database in excel. Each row in my database is usable information that has a link to Word files on our server and the database is saved on the server.
Whenever I move the original file which is on the server anywhere it causes all the hyperlinks to change. I have saved it on my desktop then moved it, and it still changes my hyperlinks. I don't understand why my hyperlinks don't just stay they way I created them.
Is there a way to keep my hyperlinks from changing? If you notice the immediate folder of the filename doesn't change.
Example:
Original hyperlink:
//server/reports/filename
Changed hyperlink:
C:/document and settings/user/microsoft/reports/filename
I have TextBox1 where I input a 1,2,3, etc...
In TextBox2, depending on the number input in Textbox1 I would like to assign a hyperlink to appear in TextBox2. I have been finding that this is nearly impossible. Are there any other viable options I should look at?
I have a database in excel that has hyperlinks to each document. The problem i have is that i need to move the documents to a new folder, therefore rendering the hyperlinks useless. I have over 1000 hyperlinks that needs to be updated.
what i need to do is the change the current hyperlink to insert the name of the new folder that these documents are to be placed in.
i.e.
c:1 management........
needs to become
c:Registered folders1 Management........
Is there a way of adding the name Registered folders to the path of the hyperlinks for every document automatically, as doing this manually will be too time consuming.
I have a worksheet with 28 sheets. I want the first sheet to be the only sheet displayed when opening the file and then want people to have the ability to click a hyperlink to unhide a particular sheet. Then when they go back to the main page (via another hyperlink), the sheet that was unhidden will be hidden again. I was able to put together some code from [URL]. When I click on a hyperlink on the main page, I get a "Subscript out of range" error and it highlights Sheets(strSheet).Visible=True from the Worksheet_FollowHyperlink code. I made a basic spreadsheet with just 3 sheets and plugged this code in and it worked fine so I'm not quite sure what the error is. I attached the spreadsheet I'm using.
View 3 Replies View RelatedHere the situation I have a list of around 2000 hyperlinks from my workbook and its various sheets. I have a pre made list of all the hyper links that there should be unfortunelty you know how things go and Im off by some here and there. What I need to figure out is how to match up my hyper link list to my pre made list. Im thinking an if statement looking for keys (truck #, Trailer #, Date) to get me the closest matches I can without having to go through every single hyper link....
Im thinking i need to match the criteria (truck #, Trailer #, Date) to that of the data i got from the hyper links. Ill explain more on my attached work sheet so it is more clear to under stand.....