Forum Discussion
Hyperlinking between pages
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
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,
9 Replies
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. 🙂
