May 26 2021 09:16 AM
May 26 2021 09:16 AM
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:
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.
May 26 2021 10:01 AMSolution
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,
May 26 2021 10:23 AM
May 26 2021 02:00 PM
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.
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
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.
May 27 2021 09:53 AM
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.
Jun 01 2021 10:22 AM - edited Jun 01 2021 11:28 AM
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!
Jun 01 2021 11:47 AM
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/
Jun 01 2021 01:13 PM