SOLVED

Hyperlinking between pages

Copper Contributor

I'm trying to maintain hyperlinks (or perhaps another way I don't know of) between two sheets. On sheet 1, I have various fields of data including building names. I want to link to a description on sheet 2. There is too much text to put on the first page, which would affect viewing. 

 

Example: On sheet 1, I have:

  • Captain Vancouver Hospital (A1)
  • Jane Goodall Theatre (A2)
  • Will Smith School (A3)

On sheet 2, the A cells will also have the building name with the B cell having a block of text describing the building. I can hyper link Cell A1 on sheet 1 to Cell A1 on sheet 2, and so on. However, this is a dynamic and evolving list. If I now insert a new building onto each sheet, Queen Elizabeth Auditorium, between Goodall and Smith, it pushes down all of the cells. I then have to update the hyperlinks in each cell every time I add a new building and description.

 

Is there a way to auto update these hyperlinks or to lock between pages? What I don't want to do is pull the long descriptive data into the first page as it would be too much. 

 

Thanks

9 Replies
best response confirmed by Coastal_Waif (Copper Contributor)
Solution

@Coastal_Waif 

 

First, I think the term "hyperlink" isn't really what's going on in Excel, although in this age of (internet)-hyperlinks, your use of it can be excused. In Excel, it can be more simply, "link" or "linked".  Or even more conversationally, one cell can "refer to" others, can "look things up" based on "references"

 

All of that having been said, let's move on from semantics to your actual workbook. First question: is it possible to post a copy (or a mock-up, if the original contains confidential or proprietary data) of the actual? That would help us help you.

 

And if you could describe more fully the whole context into which these two sheets fit, that would be very helpful as well. I wonder, for example, why you appear to think it necessary to "insert a new building onto each sheet, Queen Elizabeth Auditorium, between Goodall and Smith"?

 

It may well be that you're doing this to keep things in alphabetical order, but you're doing it "manually"--adding a row yourself--rather than just adding the new entity to the bottom of a "raw" database, and then letting Excel do the "heavy lifting." It's a common mistake, but fails to take advantage of Excel's abilities to manipulate data tables, with the end result that you (the user) are doing far more of the work to organize the data than you need to. In effect, you're just using Excel as a word-processor when you do that.

 

Having your sheet 1 lookup that descriptive data from sheet 2 is probably a simple matter (VLOOKUP, XLOOKUP are functions that readily do that), but before we get to that let's get the big picture of what this database is for, how it gets updated, how it's organized....

 

And seeing your actual workbook--if that is possible--would go a long way,

@mathetes,
Very good point. I'm an expert Word user so my brain tends to default there. You're right, I could make it easier on myself and add new entries at the bottom, then sort. I've been using Ctrl K to enter the hyperlinks to the other page. I would attach the sample but I can't see a way to attach any docs here.

When I looked at VLOOKUP it seems to only pull the data from one page to the first page, which is what I didn't want. If I can find a way to show the workbook, I'll do so.

@mathetes Ah here we go. :)

@Coastal_Waif 

 

OK. I've created something that may not be at all what you want... It can readily be modified, and my main goal was to show you what can be done.

 

There are a couple of things that I want to draw your attention to.

  1. I changed both of the tables in your two tabs to Excel Tables (automatically they get the headers and alternating colors in the rows). This also means you can add a row and it will automatically be included in the VLOOKUP on the "dashboard" page [of which, more in a moment].
  2. In order to keep things dynamic, there's a new tab called "Bus Tbls" that currently has only one table on it, and that takes care of itself, adding names, in alphabetical order, as they're added to the Project table

Now to the Dashboard tab, created to show how you can separate Output from Input. Your two existing tables are the raw data, or input. The output can be made prettier (certainly made prettier than I've done)... what it does is

  1. Prompt you to select a name for the project to display  (Click on the little arrow to the right of the yellow field)
    • That list of names is kept up to date with any new ones added to the Project table (you need to make sure that you type exactly the same name in to that Description table)
  2. Once you've selected a name, all of the associated data appears by means of VLOOKUP.
  3. Across the top (which could be hidden for actual use) are a set of numbers, used by VLOOKUP to get data from the right column.

Play around with this, but then please come back with any questions or with requests for different layouts, to the extent that you need help. As I said, I'm just trying to demo what can be done.

@mathetes 

 

Thanks so much. This will do. Now, a couple of questions. I didn't know there was a specific setting of Excel tables. Since my current document has over 100 projects, I could transfer all of the data to your template (and maybe that's easiest) but for future spreadsheets, how do I do this? (I know I have gaps in my Excel basic knowledge)

 

Also, on the Dashboard tab, the description header is offset by one cell to the other columns. Does it need to be, and would moving it up one cell break it?

 

And last, can I copy the Dashboard and Bus Tabls into tabs on my current spreadsheet, or...what would I need to do to use this setup on other spreadsheets? Feel free to direct me to a good instructional video if that helps. My previous searches didn't get me to where I wanted to go. :)

 

And thank you! This is so helpful. 

Hi @mathetes,
I have managed to put my actual data in and get the Bus Tables working and the lookup part of the dashboard, but I'm unable to get the description to load. Actually, I can get the description from table 2 but not the data from the other rows in table 1. If you have time, could you guide me on this last step? I've looked up some tutorials but am still stuck. Thank you!

@Coastal_Waif 

 

I can't give much specific guidance without seeing your sheet itself. It might have to do with the VLOOKUP formula looking for a table by name, and you haven't created (or named) that table.

 

Is it possible to post your actual table? Or does it contain confidential information that would preclude posting it publicly?

 

Here's an internet resource that can help with tables (and lots of other aspects of Excel): https://exceljet.net/

 

 

 

Hi @mathetes,

It took a couple of hours but I was able to figure it out. Part of the issue was that the column numbers were missing so it wouldn't tabulate all of the information. I have it working now. Thank you again for giving me a better tool than I would have come up with on my own. This works perfectly.
Great to hear!
1 best response

Accepted Solutions
best response confirmed by Coastal_Waif (Copper Contributor)
Solution

@Coastal_Waif 

 

First, I think the term "hyperlink" isn't really what's going on in Excel, although in this age of (internet)-hyperlinks, your use of it can be excused. In Excel, it can be more simply, "link" or "linked".  Or even more conversationally, one cell can "refer to" others, can "look things up" based on "references"

 

All of that having been said, let's move on from semantics to your actual workbook. First question: is it possible to post a copy (or a mock-up, if the original contains confidential or proprietary data) of the actual? That would help us help you.

 

And if you could describe more fully the whole context into which these two sheets fit, that would be very helpful as well. I wonder, for example, why you appear to think it necessary to "insert a new building onto each sheet, Queen Elizabeth Auditorium, between Goodall and Smith"?

 

It may well be that you're doing this to keep things in alphabetical order, but you're doing it "manually"--adding a row yourself--rather than just adding the new entity to the bottom of a "raw" database, and then letting Excel do the "heavy lifting." It's a common mistake, but fails to take advantage of Excel's abilities to manipulate data tables, with the end result that you (the user) are doing far more of the work to organize the data than you need to. In effect, you're just using Excel as a word-processor when you do that.

 

Having your sheet 1 lookup that descriptive data from sheet 2 is probably a simple matter (VLOOKUP, XLOOKUP are functions that readily do that), but before we get to that let's get the big picture of what this database is for, how it gets updated, how it's organized....

 

And seeing your actual workbook--if that is possible--would go a long way,

View solution in original post