Recently, Bluetaurean asked in the Chandoo.org Forums about ways to allocate work durations for various product lines across 24 hour days to create a daily schedule.
Both formula-based and VBA-based solutions were offered.
Today at formula Forensics we will take a look at the formula-based approach.
As always at Formula Forensics you can follow along, Download Here – Excel 2007-2013.
Set the Scene
Since one might encounter a similar need in a variety of contexts (manufacturing, engineering, project planning, etc.), we will look at a more general problem of allocating a set of tasks and corresponding durations to one or more days, as shown in the following diagram.
We will create two output views:
- One that is a flat list that can then be manipulated further using Excel’s Pivot table feature, and
- Another view that mimics a pivot-table (and is similar to a typical project Gantt view, but with actual values listed instead of a bar chart).
You can follow along using the attached Excel document. Download here Excel 2007+
Problem Specifics
- We have a list of tasks and their durations.
- We need to distribute the tasks to different days, without exceeding the maximum available duration in a given day.
- When the hours in a day are “used up”, we need to allocate the remaining task duration to the next day, and so on.
- On the other hand, if a given task does not use up all of the hours in a given day, we will need to assign more than one task for that day, provided the combined durations do not exceed the available hours for that day.
- In other words, we will need to split a task across one or more days, or combine one or more tasks into a single day, as needed, to maximize the work performed in a given day.
Developing the Approach
Before we tackle this problem in Excel, let us review how we might do this manually. Like most things, we might use the following three step process:
- Take the first task and assign its duration to Day 1. If the task’s duration exceeds the maximum hours available in a day, allocate the portion of the duration that does not fit into Day 1 into Day 2.
- Take the second task, and see whether it can fit into an existing day, or whether it needs to be distributed to multiple days
- Etc. (OK… so that three-step process was a stretch!)
Statistics show that most people think in terms of IF-THEN-ELSE statements. So here it is…
For a given Day, and for a given Task, If [Hours Not Allocated For that Task] > [Hours Available for that Day] Then Set Duration for that Day as [Hours Available for that Day] Else Set Duration for that Day as [Hours Not Allocated for that Task] EndContinue the above evaluation until all tasks have been allocated to days.
Of course, the above IF() logic can be condensed as follows:
MIN( [Hours Not Allocated For that Task] , [Hours Available for that Day] )
Putting it All Together: Output Option 1: Gantt-like View
Let us employ the above approach to create the Gantt-like view.
To make our approach more generic, we will use an Excel Name called “MaxHrsPerDay” to indicate the maximum available hours in a given day. (In the sample worksheet, it has been set to 24 hours.)
Our source data is setup as shown in the diagram below:
- Tasks are in the range A2:A5
- Durations are in the range B2:B5
We will create the output in a separate worksheet, in the range A1:E5 as shown below:
Put the following formula into cell A2 and copy down to A5:
=SourceData!$A2
(This formula is merely referencing the values from the SourceData sheet. The sample workbook also includes an approach to make this reference more location independent.)
Put the following formula in cell B2, and copy it down and right:
=MIN((SourceData!$B2-SUM($A2:A2)), (MaxHrsPerDay-SUM(B$1:B1)))
Setup the header row (B1:E1) as desired. (I have used text values for the header. You could also calculate the header text using formulas. Since that is straightforward, I will leave that as an exercise for the reader.)
Now let us look at what the formula in cell B2 is doing:
- SUM($A2:A2) is calculating the sum of the allocated durations for TaskA. (Please note the use of absolute and relative references. The formula is anchored on column A, but the starting row, ending row and ending column are free to expand.) SUM($A2:A2) returns zero since SUM() ignores text values.
– If you look at cell C2, the reference changes to SUM($A2:B2).
– In cell B3, the reference changes to SUM($A3:A3). You get the idea
- (SourceData!$B2-SUM($A2:A2)) calculates the difference between the duration for TaskA (40 in the example) and the hours allocated as of that point (0), to return 40-0=40.
- SUM(B$1:B1) is calculating the sum of the allocated hours for Day1. (Again, we are using a combination of absolute and relative references to keep the calculation anchored on column B.) In this case, the value is zero, since this is the first allocation for Day1.
- (MaxHrsPerDay-SUM(B$1:B1)) calculates the hours remaining (i.e. available) for Day1. Since this is for cell B2, the calculation returns 24 – 0 = 24.
That is it!
We put those absolute and relative references to good use!
This approach was easy because all we had to do was calculate the duration for a given task for a given day.
On the other hand, if we had to figure out what the Task was, or which Day it was, the calculation gets a little more involved. Since this is “formula forensics”, we would not have it any other way! 🙂
Putting it All Together: Output Option 2: A Sequential List of Tasks and Durations for Each Day (i.e. a Flat List)
As before, we will use the Excel Name “MaxHrsPerDay” to refer to the maximum hours in a Day.
As shown in the following diagram, we will turn the source data into a flat list of Days, Tasks and Durations:
Unlike with VBA, since a formula cannot choose which row and column to write its output, we have to set the formula in every cell where we suspect there might be a value.
In the above sample diagram, we copy the formulas from row 2 to row 9. However, row 9 shows “…” indicating that the list was completed by row 8.
Let us look at how to determine the value for Day, Task and Allocated Duration.
For ease of description, I have created the following Excel Names:
WorkList: =A2:A5 in the source data.
WorkDuration: =B2:B5 in the source data
While creating the Gantt-like view earlier, we were able to take advantage of the static “Day” and “Task” values to determine the Remaining Duration, Available Duration, etc. Since we now have to determine all three values (Day, Task, Allocated Duration), we will need some “helper” data.
We will add a column alongside the source data that shows the cumulative duration (for reasons that will become clear shortly), as shown in the following diagram:
Cumulative Duration is calculated as the sum of all durations up to a given row.
- For example, in cell C2, the Cumulative Duration is 40.
- In cell C3, the Cumulative Duration is 40+20=60
- And so on.
For ease of referencing, we will use an Excel Name called CumulativeDuration =C2:C5.
Let us look at why we need the “CumulativeDuration” helper column:
The circular logic problem
In order to determine the durations already allocated for a given day, we will need to know which Day it is.
We also need to know which Task we are trying to calculate the duration for.
So… do we calculate the Day or the Task or the Duration first?!! As you can imagine, that will soon land us in some circular logic.
Some helpful observations about the output:
- In column C of the output (on worksheet FlatList), the sum of allocated durations adds up to the total duration for all tasks. (No surprise here!)
- If every task had duration equal to the MaxHrsPerDay, you would have the same duration value for all days. (Not surprising, but interesting!)
- In other words, you could think of the Allocated Duration column as the total duration for all tasks, allocated MaxHrsPerDay at a time.
- Now we need a way to iterate through the duration values one at a time and account for the durations already processed. In other words, each value needs to contain all of the previous values. Welcome to an array of the cumulative durations!
- For example, in the cumulative array “{40;60;65;80}”, the value 60 already includes the previous value 40 in it. This allows us to subtract all durations allocated up to a given row, to get the duration value that is remaining to be allocated.
- Since Excel is good with numbers, we will base the calculation for AllocatedDuration and Tasks on the Duration values.
- By calculating the two values separately, we avoid the circular logic.
Let’s now look at the formulas for Day, WorkItem and AllocatedDuration.
It would be easier if we looked at the formulas in reverse order, starting with AllocatedDuration, then WorkItem, and finally Day.
Formula for “AllocatedDuration”
Enter the following formula into cell C2, ending with Ctrl+Shift+Enter, as shown in the following diagram:
=IF(SUM(C$1:C1)>=SUMPRODUCT(WorkDuration), “…”,MIN(INDEX(WorkDuration, MATCH(TRUE, CumulativeDuration-SUM(C$1:C1) > 0, 0)) – SUMIFS(C$1:C1, B$1:B1,B2), MaxHrsPerDay-SUMPRODUCT((A$1:A1=A2)* IF(ISNUMBER(C$1:C1), C$1:C1, 0)))) Ctrl+Shift+Enter
Let us look at the formula closely (using the formula in row 2):
- SUMPRODUCT((A$1:A1=A2)* IF(ISNUMBER(C$1:C1), C$1:C1, 0)) -> This calculates the sum of all allocated durations up to the previous row, where the Day = current row’s day. Please note the use of absolute and relative references. They allow us to expand the range as we go down the rows, while remaining anchored to the first row.
– Since this is the first data row, C$1:C1 returns “Allocated Duration” and the ISNUMBER() function returns FALSE, and consequently, the IF() function returns 0.
– A$1:A1 returns “Day”, and the test A$1:A1=A2 returns FALSE. Please note that in this case, it does not matter whether A2 has a value in it, whether it has the value 1, etc.
– SUMPRODUCT() provides the result of FALSE * 0 = 0
- MaxHrsPerDay – SUMPRODUCT((A$1:A1=A2)* IF(ISNUMBER(C$1:C1), C$1:C1, 0)) -> This calculates the difference between maximum duration available for a day and the sum of durations allocated for the current day. In other words, it calculates the available duration for the current row’s day.
– In this example, the calculation results in MaxHrsPerDay (24 in our example) – 0 = 24
- SUMIFS(C$1:C1, B$1:B1,B2) -> This calculates the sum of all allocated durations for the current row’s task. Since B$1:B1 is the text value “Work Item”, the SUMIFS() returns 0. Again, it does not matter if B2 is blank or has a value like “TaskA”, since Excel correctly evaluates the condition whether B$1:B1 equals B2.
- SUM(C$1:C1) -> This calculates the sum of all allocated durations up to the previous row.
- CumulativeDuration — SUM(C$1:C1) -> CumulativeDuration evaluates to {40;60;65;80}. SUM(C$1:C1) evaluates to zero. As such, the expression evaluates to {40;60;65;80} – 0, or {40;60;65;80}.
– If we look at the calculation for this expression in cell C3 (the expression would be “CumulativeDuration—SUM(C$1:C2)”), we would get the result of {40;60;65;80} – (0+24) = {16;36;41;56}. (As you know, subtracting a scalar value from an array results in an array with each value reduced by the scalar value.)
– If we look at the calculation for this expression in cell C4 (the expression would be “CumulativeDuration—SUM(C$1:C3)”) , we would get the result of {40;60;65;80} – (0+24+16) = {0;20;25;40}
– As you can see, each successive calculation reduces the CumulativeDuration array by the amount of hours already allocated. By reducing the CumulativeDuration array in this fashion, we ensure that we do not “double count” a duration.
– If a value in the array evaluates to zero, it means the corresponding duration has been fully allocated. (In cell C3, the first value in the array is zero, indicating that the original 40 hours has been fully allocated.) We will put this knowledge to good use in the next expression.
- MATCH(TRUE, CumulativeDuration—SUM(C$1:C1) > 0, 0) -> The expression CumulativeDuration—SUM(C$1:C1) > 0 evaluates to ={TRUE;TRUE;TRUE;TRUE} because all values are greater than zero. By performing a MATCH() for TRUE, we are able to find the first location in the array that has a non-zero value.
– If we look at the result of this expression in cell C3, we get {16;36;41;56} > 0 = {TRUE;TRUE;TRUE;TRUE}
– If we look at the result of this expression in cell C4, we get {0;20;25;40} > 0 = {FALSE;TRUE;TRUE;TRUE}
– As you recall, the zero values (or FALSE) correspond to the durations that have been fully allocated, whereas, the non-zero values (or TRUE) correspond to the durations that have NOT been fully allocated.
– It is helpful to note that MATCH() returns the LOCATION of what it finds. As such, the returned location is that of the first duration value that has not been fully allocated! Since the CumulativeDuration array is the same size as the WorkDuration array, we will be able to put this returned location value to good use in the next expression.
- INDEX(WorkDuration, MATCH(TRUE, CumulativeDuration — SUM(C$1:C1) > 0, 0)) -> By using the location value (of the first duration value that has not been fully allocated), we find the corresponding original duration value from the WorkDuration array.
– As we saw earlier, the expression “CumulativeDiration – SUM(C$1:C1)” reduces the CumulativeDuration by the duration values allocated to that point. However, the resulting array could have partial duration values as well. By referencing the corresponding duration value from the WorkDuration array, we ensure that we retrieve the original (full) duration value that was to be allocated.
- MIN(…) -> This expression calculates the value of MIN([Hours Not Allocated For that Task], [Hours Available for that Day])
– [Hours Not Allocated For that Task] is returned by INDEX(WorkDuration, MATCH(TRUE, CumulativeDuration—SUM(C$1:C1) > 0, 0)) – SUMIFS(C$1:C1, B$1:B1,B2)
– [Hours Available for that Day] is returned by second half of the MIN() expression: MaxHrsPerDay—SUMPRODUCT((A$1:A1=A2)* IF(ISNUMBER(C$1:C1), C$1:C1, 0)).
– So, we essentially got back to the logic we started from, which is the same logic we used for creating the Gantt-like view as well.
- The remaining portion of the formula (the IF() check) determines if all of the hours have been allocated. If all hours have been allocated, it returns “…”.
– SUMPRODUCT(WorkDuration) -> This expression calculates the total of all work duration values. In cell C2, it evaluates to SUMPRODUCT({40;20;5;15}) = 80
– SUM(C$1:C1)>=SUMPRODUCT(WorkDuration) -> Determines if the sum of durations allocated up to that point is greater than the total for all durations. (Since this is part of an array formula, you could also use the SUM function in place of SUMPRODUCT. But I am partial to the SUMPRODUCT function!! So, unless you are in a competition where the winner is determined by the shortest formula, feel free to use either one!
Formula for “WorkItem”
Enter the following formula into cell B2, ending with Ctrl+Shift+Enter, as shown in the following diagram.
=IF(SUM(C$1:C1)>=SUMPRODUCT(WorkDuration), “…”,INDEX(WorkList, MATCH(TRUE, (CumulativeDuration-SUM(C$1:C1)) > 0, 0))) Ctrl+Shift+Enter
You are already familiar with most of the formula components since you saw them in the formula for AllocatedDuration. The only difference is that in this formula, we are returning a value from WorkList. (i.e. we locate the position of the first non-zero duration in CumulativeDuration array, and since that array is the same size as the WorkList array, we are able to find the first Task that has not been fully allocated.)
Formula for “Day”
Enter the following formula into cell A2, ending with Ctrl+Shift+Enter, as shown in the following diagram:
=IF(SUM(C$1:C1)>=SUMPRODUCT(WorkDuration), “…”, MAX( N(A1) + (SUMIFS(C$1:C1, A$1:A1, A1)>=MaxHrsPerDay), 1)) Ctrl+Shift+Enter
Let us look at the formula in detail (using the formula in row 2):
- SUMIFS(C$1:C1, A$1:A1, A1) -> This expression calculates the sum of all durations (in column C) where the Days (in column A) equal the previous day.
– In cell A2, this expression evaluates to “SUMIFS(“Allocated Duration”, “Day”, “Day”)” = 0. (Excel smartly ignores any non-numeric values in the first argument.)
– In cell A3, this expression evaluates to “SUMIFS({“Allocated Duration”;24}, {“Day”;1}, 1)” = 24.
- SUMIFS(C$1:C1, A$1:A1, A1)>=MaxHrsPerDay -> This expression checks if the sum of all durations where the Days equal the previous day is greater than or equal to MaxHrsPerDay.
– In cell A2, this expression evaluates to FALSE
– In cell A3, this expression evaluates to TRUE
- N(A1) -> This expression returns the numeric value for its argument. Since N() returns zero for any non-numeric arguments, we use this function to return zero for the heading (“Day”) in A1. (Any numeric values are returned as is.)
- MAX( N(A1) + (SUMIFS(C$1:C1, A$1:A1, A1)>=MaxHrsPerDay), 1) -> The first argument of the MAX function “N(A1) + (SUMIFS(C$1:C1, A$1:A1, A1)>=MaxHrsPerDay)”returns the next increment for day, if the previous day has been fully allocated. Otherwise, it returns the same value as the previous day.
– In cell A2, this expression evaluates to MAX( N(“Day”) + (SUMIFS(“Allocated Duration”, “Day”, “Day”)>=24), 1), which evaluates to MAX( N(“Day”) + (0>=24), 1), which evaluates to MAX( 0 + (FALSE), 1), which finally evaluates to 1.
– In cell A3, this expression evaluates to MAX( N(1) + (SUMIFS({“Allocated Duration”;24}, {“Day”;1}, 1)>=24), which evaluates to MAX( N(1) + (24>=24), 1), which evaluates to MAX( 1+ (TRUE), 1), which finally evaluates to 2 since 1 + TRUE = 2.
Download
You can download a copy of the above file and follow along, Download Here – Excel 2007-2013.
Final Thoughts
While we used the same basic logic for both output options in this article, there are probably many other ways to tackle the age-old problem of production scheduling.
I would love to hear about some of your ideas, as well as ways to extend the concepts described here.
In the meantime, I wish you continued EXCELlence!
Sajan.
Other Chandoo.org Posts related to Scheduling
Here at Chandoo.org you can find the following related posts:
http://www.chandoo.org/wp/2010/11/18/scheduling-variable-sources/
http://chandoo.org/wp/2009/06/16/gantt-charts-project-management/
http://chandoo.org/wp/project-management-templates/gantt-charts/
Thank You
This was Sajan’s second post at Chandoo.org and so a special thank you to Sajan for putting pen to paper to describe the technique here.
You may want to read Sajan’s first post here or thank him in the comments below:
Formula Forensics “The Series”
This is the 31st post in the Formula Forensics series.
You can learn more about how to pull Excel Formulas apart in the following posts: Formula Forensic Series
Formula Forensics Needs Your Help
I need more ideas for future Formula Forensics posts and so I need your help.
If you have a neat formula that you would like to share like above, try putting pen to paper and draft up a Post like Sajan has done above or;
If you have a formula that you would like explained, but don’t want to write a post, send it to Hui or Chandoo.
84 Responses to “Beam Me Up Scotty – Excel Hyperlinks”
I've just started using the formula version "=HYPERLINK("S:\Reserving\Management Judgment comments\check for MJ comments TOS 006 "&TEXT(month,"mmmm yyy")&".xls","MJ Comments")" and making it dynamic as some of the files that I use have a new name each month, or even each day.
I have an Excel file called "Important Stuff'. Rather than use post it notes, I put information I need into this file. I create hyperlinks to the files I most reference; especially when I have many different versions. I then add comments in the cells next to the hyperlinks to tell me what the differences are. I rarely have to search as everything is right there in the one file.
Great article! I'm bookmarking this for reference. Thanks for putting 'all you need to know about hyperlinks' in one place.
Hyperlinks are great but "Hyperlinks for navigating a spreadsheet are lost if saved as a "pdf". Even if you are utilizing Adobe Writer 8.0.0"
Great info! I never thought of using hyperlinks in Excel. Mostly used them in Word, email and especially in Powerpoint.
One cool thing I do is combine the hpyerlink function with a custom function I had help in building. I have a list of contractors from our Accounting Software (Quickbooks). We keep scanned copies of the 1099s in one folder. We can then download the data, which includes the vendors TaxPayer ID. The formula then checks for the file. If it exists, it provides a link to open it. If it doesn't it says so.
Here is the custom function code:
>>>>>>>>>>>>>>>>>>>>>>>>>
Public Function MyFileExists(MyFilePath As String) As Boolean
Dim objFSO As Object
Set objFSO = CreateObject("Scripting.FileSystemObject")
MyFileExists = objFSO.FileExists(MyFilePath)
Set objFSO = Nothing
End Function
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Bobby
BobbyBluford.com
This is aewsome. I have been using hyperlink since a long time. I like this option as this helps me create my Dashboard like a webpage. Thanks for sharing this, I got more insight of this option. 🙂
Bobby: Fantastic idea! What's amazing is as soon as I started reading 'scanned copies' in your post I was thinking 'hey that would be cool if you could just link to the files if and only if they exist'. Then you described how you do exactly that!
Nice one.
I use hyperlinks to jump from one location in a tab to another. When I add rows, the hyperlink destination location does not reflect the rows I just inserted. Does anyone know a way around this?
@All
If you like playing pranks on you co-workers or friends here is a simple Excel Hyperlink prank.
.
Open a workbook, probably not an overly important one, and select a page then either Use Ctrl A twice or select all the cells by clicking in the area to the upper left of A1.
Right click any cell and insert a Hyperlink, doesn't matter what its to, Another page, a Web Site or Send an Email
close the file
.
When anybody opens the file and clicks anywhere on the page it will execute the Hyperlink, even on a blank cell, where the Hyperlink isn't shown
This works particularly well on Blank worksheets
.
To remove right click anywhere on the page and Remove Hyperlinks
I have been wrestling hyperlinks to PDF files for over a year now, I can insert hyperlinks fine, and they look beautiful, but when click on them it comes up with "Cannot Open the Specified File". Does anyone know if this is because PDFs are unsupported by Excel or can other people get hyperlinks to PDFs working OK.
My application is a register of approved capital expenditure projects and the link is to a PDF scan of the signed approved document.
Did you add ".pdf" when you created the link?
I know this is an old comment, but we had the same problem too and the solution takes a little digging.
1. Open Adobe Reader X
2. Pick "Preferences" from the "Edit" menu
3. Pick the "General" category on the left.
4. Uncheck the "Enable Protected Mode at startup" box at the bottom.
5. Close Adobe Reader and retry opening the PDF file from Excel and it should work now.
Abobe Reader XI has a similar problem with a different error message. The settings in Reader XI seem to be Edit > Preferences > Security (Enhanced). At first I tried unchecking "Enable Protected Mode at startup" as this was the fix in Acrobat X. This did not work for me the first time. Then I tried unchecking "Enable Enhanced Security" and it started working. Then to test it, I re-checked both boxes... and it still works. I am not sure if other settings changed along the way, so I can't confirm the resolution other than "try this and see if it works!"
@JJ
What version of Excel are you using ?
and
What PDF Reader are you using ?
I assume you are using the Link to an Existing File or Web Page dialog?
Because that has worked as described for years without error
I am using excel 2007
The PDF reader I am using is Adobe Reader X.
So nothing really unusual.
I am using the Insert Ribbon, then the Hyperlink Icon.
I just had anohter play with it - it seems to work OK if the PDF file is on my C drive, but as soon as the PDF file is on a network drive it comes up with the error.
[...] Introduction to Excel Hyperlinks [...]
Dear Bobby i think your solution is very near to my requriment but can please explain in layman terms . I have a set of files in a folder let say a,b,c,d,e and i have a range of column in excel a1,b1,c1,d1,e1 .So if i click cell a1 file "a" should open. For range of 5 cells we can hyperlink each cell but if i have 100s of cells and files How can i apply hyperlink to all of them at once please explain
I'm using excel 2010 & adobe reader 10 - getting the same error "Cannot Open the Specified File” when linking to a file on a networked drive. File opens fine if it's on my PC.
Hope this helps.
Say in cell Z1, I have the URL: http://www.microsoft.com
And, I have a rectangle shape near A1... and I want it to have a hyperlink... and the URL for the hyperlink should be the URL in cell Z1.
So, when someone clicking the Rect will be taken to http://www.microsoft.com
When the URL is changed in cell Z1 to say http://www.yahoo.com
Now, when clicking the Rect should take him to the new URL.
How to achieve this? Thanks.
I am using Excel 2007. I have a spreadsheet with hyperlinks that were created using formulas, such as =HYPERLINK( Link Location, Name).
The hyperlinks work fine while I am in Excel, so I know they are active. However, when I use Acrobat 10 Pro to pdf the spreadsheet (and select the option to "Add Links") the Web links are no longer active.
I imagine it's because Acrobat cannot handle the "behind the scenes" commands such as a formula, and then just uses the visible text in that particular cell.
Has anyone found a work-a-round for this problem?
The problem is that the hyperlinks in Excel are nothing more than cell references. However the cell disappear when saving as .pdf.
In Powerpoint, the slide references can convert to pdf and continue to work.
[...] Hyperlinks: for accessing other parts of the workbook & data [...]
I have a single page dashboard created, where I would like each of the graphs to serve as a hyperlink to the actual data that creates the graph. The problem I'm running into is the URL for the hyperlink can be very long, depending on the data used to create the graph. Does anyone know how to get around the 255 characters limit Excel has on URLs? I used =Hyperlink(A1)&Hyperlink(A2) without Excel returning an error, but when I click on the link, it doesn't do anything. Any help would be greatly appreciated.
Brad,
I realize this comment is about 4 1/2 years old and you probably won't see this. But for anyone else who reads this, what about a URL shortening service like bitly, tinyurl, etc.?
I need to use hyperlink in excel to brows a web page according to each row
of a table that it's data comes from a external source such a sql connection.
but the text I have genarated from sql query "=hyperlink("Http://somthing.com","WebPage")"
appearing just like above text if i edit i in sheet for example adding some space after of before text and accepting changes then link become a real link
any kelp!
I would be appreciate
The simplest way to add web page links is:
1. In your browser, copy the url
2. Paste the url into a cell
3. Press F2
Done!
Hi Gents
i don't have not much knowledge about hyper link.Could please any
one can explain how hyper link has been working.
@Mohanraj
A hyperlink is like a shortcut
By selecti8ng the hyper link it takes you somewhere else
Hyperlinks can:
Take you some place else, like another sheet or workbook or open other file, they don't even have to be an Excel file
Hyperlinks can be placed in cells, ranges or objects in Worksheets like Charts or Rectangles etc, all of which trigger the hyper link when you click them
The post should tell you the rest of the story on how to use them
Hi I am trying to insert an activex control from Developer tab and add hyperlink (web address) to it. Hyperlink button is not enabled in Excel 2010. The same option is available in Excel 2007. I am trying to this by C# code using this function.
ActiveSheet.Hyperlinks.Add(ActiveXObject,"http:\\www.gmail.com",Type.Missing,"Screen Tip",Type.Missing);
This code throws 'invalid argument' exception. I tried to use 'Application.Selection' for anchor as first argument with out any success.
Any help is appreciated.
Regards,
Thanks with regard to providing these types of
good information.
Please tell any body..
Can we add a shortcut key as address of a hyperlink in Excel so that when we click on that hyper link the shortcut key works.
[…] Introduction to hyperlinks […]
I have an excel sheet with quite a lot of hyperlinks to files and folders in the server. We will be implementing new servers as part of a project, which means the directory structure of the old server will be moved to the new one.
Is there an easy way to keep the existing hyperlinks intact when moving to the new server? or do I have to edit each hyperlink and start all over again?
Your help on this much appreciated.
Cheers
ST
useful information
trying to hyperlink a cell with an object placed in shared folder.
It only kinks the shared folder but not the object in it...help!!!
I love the details on your websites. Thanks for your time!
Pl show me how to add a hypelink inside a text box, shape & image
so when I click any of the above it directs to hyperlink destination
Thanks
kanil
@Kanil
Simply insert a shape
Right click on the shape and select Hyperlink
I am trying to insert a .pdf file into an Excel spreadsheet. I have a large library of .pdf part drawings located on our server.
Path = Z:\MachineShop\Administration\Customer Drawings\
I would like the hyperlink to use a concatenated cell (C19) which contains =CONCATENATE(QUOTE!C1, QUOTE!D19)) QUOTE!C1 is the name of the file minus the .pdf suffix and QUOTE!D19 contains the .pdf suffix which is added to the part number to complete the file name. I would like the hyperlinked image to automatically load when a new value (part number) is entered into QUOTE!C1. Is this possible?
Trying to display only text if a parameter is/isn't found on a positive or negative IF response when using the IF and HYPERLINK functions together. If looking for a "No" value (positive) or if looking for a "Yes" value (negative); both are written correctly, to include the VBA macro, and what should drive a text-only response supplies a hyperlink that shouldn't be a hyperlink. It goes like this:
With Table1:
Positive No response:
=IF($O2="No", "Missing", IF($O2="Yes", HYPERLINK(pathname(), "Open"), HYPERLINK(alternatepathname(), "Open")))
Negative No response:
=IF($O2="Yes", HYPERLINK(pathname(), "Open"), IF($O2="Yes", HYPERLINK(alternatepathname(), "Open"), "Missing"))
Well, based on both responses, if Missing is displayed, it is a hyperlink, that points to nothing, instead of text. It looks like a flaw in Excel's coding. Is there a way to force just text to display?
As soon as I posted this, I found the answer.
The desired text-only response should still be writted with a HYPERLINK since one HYPERLINK function makes the entire cell followable regardless of the answer. In my case, I should write my formula like so:
=IF($O2=”No”, HYPERLINK("", "Missing", IF($O2="Yes", HYPERLINK(pathname(), "Open"), HYPERLINK(alternatepathname(), "Open")))
Having applied this, the hyperlink "look" still exists, but you can format over the cells. You don't have an active hyperlink, but you would still get the "pointer hand" like it were a hyperlink.
Hi,
I have an issue with Excel 2013. I am trying to add Navigation buttons to move around my workbook and I can't. I link the hyperlink to the place I want and try clicking the newly placed button/hyperlink and it throws up this Error Box message: ''Your organisation's policies are preventing us from completing this action for you. For more information please contact helpdesk.
Any ideas of what to check or change in Excel to be able to use hyperlinks? It was working a few days ago and now this nonsense!!
Thanks.
Without having Excel in front of me, you may want to check a few things.
File permissions in the folder your document is saved.
Make sure it isn't read-only.
If it is opened as an attachment, save it to your desktop first.
If it is saved to a network folder, try saving to your desktop.
See if you can add a trusted location for the document in Excel.
If you are still prohibited from this functionality, your group policy setting applied to your machine are probably restricting this action. You may have to look into whether you really need this functionality.
Hi All,
I have a question:
Is it possible to set hyperlink to an object?
What I mean is to follow the link to an object, not to put on an object.
For example, I have 10 pictures on my worksheets, I would like to set 10 hyperlinks on 10 cells (namely Pic 1, Pic 2, etc.) that will lead me to the corresponding pictures.
Is that possible?
Thanks in advance for your advice.
Cheers, 🙂
@MF
Yes,
Right click on the item, which can be a chart, shape, cell and Add/Insert Hyperlink
Set the location as a Range, Web address, external file etc
Hi Hui,
Thanks for your quick response.
However I want the link to be put on cell that will lead me to the object; not the other way.
Is it possible?
Why not put the pictures in a cell and link to the cell the picture is in?
Because the position of the object will be moved from time to time; and the picture is too big to be put into a single cell... 🙂
I link to charts often, and just merge cells or link to the upper left corner.
If it moves, that is trouble. Your best bet may be to run a macro to select it:
Sub SelectPicture()
Sheets("MyTab").Shapes("Picture 1").Select
End Sub
Thanks Mike for your suggestion.
I will give it a try. 🙂
I couldn't get the hyperlink formula to work and then found on some forums to put "#" before the sheet name..
so =HYPERLINK("#SHEET1!A1","WHY IN THE WORLD DOES THIS WORK")
does anyone know what the "#" does and why it is necessary?
thanks
Hi,
I have one excel document with four worksheets. The first sheet has the hyperlinks to the other worksheets. The issue I have is when I click on the hyperlink, it takes me directly to the source I need, but i find it hard to locate where the hyperlink has taken me on the screen, due to many cells of information being on the screen. So I'm wondering is there anyway of highlighting the cell that the hyperlink takes me to (ie: the source) or highlighting the borders of the source once I jumped there.
Hi
I have a workbook that I have created and am using a hyperlink to navigate to another page however I would like the info from the previous page NOT to save is there a way to do this?
@Angela
You could probably do this using some VBA Code
I'd suggest posting the question on the Forums and attach a sample file
http://forum.chandoo.org/
Explain exactly what you want to achieve
I am trying to create a hyperlink or formula that will generate an email but also include the file I am in as an attachment.
@Chandan
I doubt that can all be done by a simple Hyperlink
It could be done with a small piece of VBA Code
I'd suggest using the search box at the top right of this page as I am pretty sure that has been answered before
Thank you for the excellent tutorial. Pls help me on how to create a link in excel to open a specific page in pdf. I have use several code to do this but still take me to page of the pdf file. How do I do this to jump to a specific page directly by clicking a link from excel. Thank
@Muffliu
Try:
=hyperlink("E:\testfile.pdf#page=5")
I have an excel document with hyperlinks to a folder on my desktop. I now want to share the document, but am struggling to keep the hyperlinks working. When others open the file, I get an error that the internet site reports that the item requested cannot be found. Is there a work around that doesn't require re-hyperlinking all of the documents?
Hello,
Well we use a particular software for document management made by humming bird software. This software is supposed to be a last version Archive save. That is not what it is used for at our work though it is used as an active directory. I was wondering if anyone has recent experience with hyperlinking between two or more documents in this type of storage.
Is there a hyperlinks for dummies? I need to link a pdf in an excel spreadsheet from my PC. I want to be able to email it to send it by email and be able to open spread sheet with the link on smart phone. Please help!
Repeat the process of coloring and ironing until you like what
you see.
I have two mysteries in Excel 2010:
1. I was given a spreadsheet in which numerous cells were formatted with hyperlinks and asked to extract the hyperlinks so they could be viewed directly in adjacent cells. I never found a function that could do that and didn't have months to take a class to learn to use VBA.
2. When you type a URL into a cell and hit enter, Excel converts the formatting of that cell so that it becomes hyperlinked. I have a spreadsheet that stopped doing that. In fact, the Insert Hyperlink command has been disabled in the ribbon and in the right-click menu. (However, the =hyperlink("") function still works.) How did that happen, and how do I get the command back?
I checked File > Options > Proofing. Nothing was out of the ordinary there.
@Rich
Can you please ask the question in the Chandoo.org Forum http://forum.chandoo.org/
There are a number of ways Hyperlinks can be added and so attaching a sample file would be great and will speed up an answer
Appreciating the dedication you put into your site and detailed information you present.
It's awesome to come across a blog every once in a while
that isn't the same out of date rehashed information. Wonderful read!
I've bookmarked your site and I'm adding your RSS feeds to my Google account.
How can you make the entire cell active? Normally only the text within the cell becomes active and the remaining white space only selects the cell. Example: if the cell columnwidth is say 30 but the hyperlink textToDisplay is "XYZ" then only "XYZ" will be the active portion and all the empty space within the cell is not.
There is an exception. If the cell is formatted with an indention then the entire cell becomes active. For my purpose this exception can be used but I desire to have the cell text centered. But sometimes the cell width is just a fraction larger than the width of the text so indention would break the desired formatting. This is why I would like to have the entire cell hyperlink active and the textToDisplay centered without an indent.
When I originally commented I seem to have clicked the -Notify me when new comments are added- checkbox and now whenever a comment is added
I receive four emails with the exact same comment.
Perhaps there is a way you are able to remove me from that service?
Thank you!
Fantastic beat ! I wish to apprentice at the same time
as you amend your web site, how can i subscribe for a
blog web site? The account helped me a applicable deal. I have been a little bit acquainted of this your broadcast provided bright
transparent idea
Heya superb blog! Does running a blog such as this take a massive amount work?
I have very little understanding of programming but I was hoping to start my own blog in the near future.
Anyway, if you have any recommendations or tips for
new blog owners please share. I know this is off subject however I just needed to
ask. Kudos!
Thanks to my father who shared with me on the topic of this blog, this
blog is really remarkable.
Hello, I would like to subscribe for this web
site to take latest updates, thus where can i do it please help out.
Awesome blog you have here but I was curious about if you knew of any discussion boards that cover the same topics discussed in this article?
I'd really like to be a part of online community where I can get comments from other knowledgeable people that share the same interest.
If you have any suggestions, please let me know. Bless you!
@Promiennik
You may want to look at the Chandoo.org Forums, a very vibrant community for asking Excel questions
http://forum.chandoo.org/
With havin so much content and articles do you ever run into any issues of plagorism or copyright violation?
My blog has a lot of unique content I've either created myself or outsourced but it
appears a lot of it is popping it up all over the
web without my authorization. Do you know any methods
to help protect against content from being stolen? I'd really
appreciate it.
I am having difficulty in opening a hyperlink to a pdf that is housed on the internet (an internet hyperlink ending in .pdf). When I first click the link in excel, I get an alert which says: "Opening.....pdf. Some files can contain viruses or otherwise be harmful to your computer.... Would you like to open? I click OK, and then I get an Excel error that says "Unable to open https://......pdf. The internet site report that a connection was established but the data is not available. How do I get around this? I can copy the hyperlink out of Excel, paste it into my web browser and it opens without any problems. Thanks.
Great goods from you, man. I've be aware your stuff prior
to and you are just extremely great. I really like what you've received here, certainly like what you're saying and the
way in which through which you are saying it.
You make it enjoyable and you still care for to keep it wise.
I can not wait to read much more from you. That is actually a wonderful web
site.
I tried the FoloowHyperlink worksheet event for a hyperlink text in a cell; attached some code to it, and it worked well. When I created a second hyperlink and attached some other code to this hyperlink in another cell, and clicked it, it worked but the result was not what I expected. The second hyperlink executed the code meant for the first hyperlink.
The questions are:
1. Whether the FoloowHyperlink event works for only one hyperlink in one sheet?
2. If not, how many hyperlinks can I create in ONE sheet and attach different code to them. How?
I really appreciate your piece of work, Great post.
We're a bunch of volunteers and starting a new scheme in our community.
Your web site provided us with useful information to work on. You have performed
an impressive activity and our entire group will likely be grateful to you.
I have a Master Excel Workbook in which i have linked the relative documents using hyperlinks. These workbooks open when i click on the Hyperlink. i have also created a back Link to Direct back to the Master. But in the process when i get directed back to Master Excel Workbook the Other Workbook which opened by clicking hyperlink remains open. I want those opened workbooks to close when i click back to master file. Is there a way in which i go directly to the master Excel Workbook by simultaneously closing the other Workbook.
Appreciation to my father who told me concerning this web site,
this website is genuinely awesome.
It's a pity you don't have a donate button! I'd certainly
donate to this superb blog! I guess for now i'll settle for bookmarking and adding your RSS
feed to my Google account. I look forward to brand new updates and will talk about this site with my Facebook
group. Talk soon!
@Forex
You may want to visit this page:
https://chandoo.org/forum/threads/donate-support-our-ninjas.18150/
Awesome post.