Forum Discussion
Hyperlinking between pages
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,
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. 🙂
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.
- 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].
- 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
- 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)
- Once you've selected a name, all of the associated data appears by means of VLOOKUP.
- 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.
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!