Excel 2010 Navigation Sheet

[reaktorblue]

Wow, where to begin. I think the biggest issue I'm running into is I have no idea what to call this. The previous place where I worked has a reporting team that had this enabled somehow and I'm looking to accomplish the same.

They had all of the tabs hidden. Instead of navigating by tabs, they had basically a sidebar navigation. There were links to various reports (which were basically direct links to the other tabs) but this sidebar navigation did not scroll with the page. It had its own scroll bar.

I'm working on a project and I'm trying to attempt something similar. My navigation menu currently looks like the following.

Excel Workbook
	B	C
2	Shortcut Panel
3	My Home	Market Add Item
4	My Bazaar	Bazaar Add Item
5	My Display Case	Display Case Add Item
6	My Vault	My Events
7	My Warbase	My Faction Attacks
8	My Company Specials	My Items
9	Advanced Friends List	Advanced Blacklist
10	Stock Portfolio	Race
11
12	Player ID
13
14	Travel Qty	22
15
16	Seconds Remaining	1104
17	Time Remaining	0:18:24
18
19	Total Value
20	Total Profit
Format Test

Excel 2010

Is there any way to get this to appear on every worksheet and enabled in it's own scrolling section? I think the biggest issue I'm running into is I'm not sure what this is called. I've tried splitting the panes, but that doesn't really seem to be correct. I've tried freezing the panes too, which is a silly workaround (it works, but it's sure not pretty and sure not what I'm attempting to do.)

I was thinking I could create the navigation menu in a worksheet all of it's own and use view side by side with all of the rest of the worksheets. Creating the navigation in it's own sheet would save a bunch of time with updates to the sheet but after attempting this, it's still not exactly what I was looking for.

Does anyone happen to have any insight as to a solution to what I'm looking for?

 

[reaktorblue]

I also want to add that in the rough example I gave, with the previous spreadsheet I remember seeing (wish I had access to it still) the field headers, despite being in a different font, did not seem to affect the size of the cells in the adjacent data.

It was almost as similar as the side navigation in a website. I may be getting in a bit over my head but after a complete night of scouring the net, I've still come up with no ideas nor terminology to describe the effect I'm going for. I cant for the life of me figure out how it was done by my old employer's reporting team.

 

[reaktorblue]

Also a further elaboration on the subject, since I've attempted a few different tweaks to no avail. The sidebar was not simply just a navigation pane with quick links to reports.

It was almost as if it was it's own complete section. There were two graphs that were also on the bottom part of this pane. I'm not quite sure what to call it. It was a bit more than a simple navigation pane.

If you could picture in my example in an earlier post, that being along the side of every worksheet, that's what I'm going for.

 

[Trevor G]

Is it possible that the Hyperlinks where just copied and pasted to each sheet within the workbook setting to the same area and settings (column widths row heights etc) or do you think it might be that some VBA code was used so when you select a sheet it replicated the hyperlinks?

What happens when the workbook is opened, do you always return to the start point?

 

[reaktorblue]

Every time the workbook was opened, you were returned to what they called a dashboard. The main page had snippets of the other worksheets and the left hand menu.

The left hand menu had links to each of the worksheet tabs (although the tabs were hidden by default) a quick sum areas (like open tickets, total tickets month to date, etc) and 2 graphs.

It may have been some VBA code. At the time I never thought to really question it or dive too deep into the structure of the sheet since it was out of my scope. I wish I paid more attention to what was going on with it then.

 

[reaktorblue]

If I were to think of the way it was displayed, as a website, it would have appeared to have been in frames.

I can somewhat get what I'm looking for by simply putting everything on the side on each page and freezing those columns, although it would kind of mess up the view of the data that would be in the adjacent cells.

Even doing research on VBA related stuff seems to turn up little to no results on anything similar. I have a feeling I'm simply not using the right keywords when attempting to describe what I'm looking to do.

 

[Trevor G]

If you want a sheet to be selected each time the workbook opens you need to look at the VBA Screen (use Alt + F11) then on the project area (top left side) it will show you or worksheet names then an item that states ThisWorkBook, double click here and it activates anther sheet in the right side, at the top there are 2 drop downs select the first (General) and change to Workbook, you will then see some code stating

Private Sub Workbook_Open()
End Sub

You can add in the sheet name so when the workbook opens it will always select the dashboard sheet.

Dashbaords are basic summaries of data and graphs to give pictorial representations.

You can also set the worksheets to hidden here if you only want to use Hyperlinks to navigate.

 

[reaktorblue]

I'm not really sure to make heads or tails out of that. Picture this as what I saw and what I'm trying to accomplish.

Excel Workbook
	A	B	C	D	E	F	G
1	Main Navigation			Main Data Header
2	Nav to Tab 1	Nav to Tab 2		Data Header 1	Data Header 2	Data Header 3	Data Header 4
3	Nav to Tab 3	Nav to Tab 4		Data Value	Data Value	Data Value	Data Value
4	Nav to Tab 5	Nav to Tab 6		Data Value	Data Value	Data Value	Data Value
5	Nav to Tab 7	Nav to Tab 8		Data Value	Data Value	Data Value	Data Value
6				Data Value	Data Value	Data Value	Data Value
7	Quick Summary			Data Value	Data Value	Data Value	Data Value
8	Summary Value	Summary Total		Data Value	Data Value	Data Value	Data Value
9	Summary Value	Summary Total		Data Value	Data Value	Data Value	Data Value
10	Summary Value	Summary Total		Data Value	Data Value	Data Value	Data Value
11	Summary Value	Summary Total		Data Value	Data Value	Data Value	Data Value
12				Data Value	Data Value	Data Value	Data Value
13	Dashboard Highlights			Data Value	Data Value	Data Value	Data Value
14	Chart			Data Value	Data Value	Data Value	Data Value
15				Data Value	Data Value	Data Value	Data Value
16				Data Value	Data Value	Data Value	Data Value
17				Data Value	Data Value	Data Value	Data Value
18				Data Value	Data Value	Data Value	Data Value
19	Dashboard Highlights			Data Value	Data Value	Data Value	Data Value
20	Chart			Data Value	Data Value	Data Value	Data Value
21				Data Value	Data Value	Data Value	Data Value
22				Data Value	Data Value	Data Value	Data Value
23				Data Value	Data Value	Data Value	Data Value
Sheet2

Excel 2010

Now in this example, simply click on the hyperlinks "Nav to Tab" changes what's displayed in the "Main Data Header" section, as if it were in a completely separate frame. To the degree that the vertical scroll bar that's displayed would only affect that section as well.

The areas for "Main Navigation", "Quick Summary", & "Dashboard Highlights" in this example change the cell height of the adjacent "Data Values" which also change depending on what tab is being viewed. This is not ideal and I'm not sure how they accomplished it previously but this particular section appeared to be a spreadsheet all of it's own.

I'm completely baffled as to how my previous employer's reporting team was able to accomplish this. Once opening the spreadsheet, it would display the "Main Navigation" section but instead of the "Main Data Header" section, it was a "Dashboard Display". Simply clicking the navigation buttons would allow the reader of the sheet to navigate to more specific sets of data.

One thing I did forget to include in this example was a navigation link to "Home" but that was nothing more than another hyperlink in the "Main Navigation" area. Does this make sense? I'm not sure what to call this sort of setup or if it was done in VBA or not. I'd like to be able to mimic similar functionality. It would be almost as simple as just putting a navigation area on the left hand of each tab however, as I stated previously, it does change the dimensions of the adjacent cells and I'd like to avoid that (as it was avoided in their spreadsheet). Also, I had the ability to use a frozen pane then too which would not extend into the navigation area.

Just about every which way I can think to accomplish something even remotely similar, it's not quite the same. Either close in look but not in functionality or close in functionality but not in look.

 

[Trevor G]

Its very possible that several workbooks have been used, then arranged and then saved individually and collectively saved as a workspace.

With a workspace you can have a number of workbooks open and arranged as you would like, then when you select the View Tab on the right you will see Save Workspace. This overall gives you a different way to work with multiple workbooks.

Suggestion.

Have 2 workbooks open and then use the View Tab and Arrange windows Vertically. Place some stuff in the workbooks to make them different and save each one as you see it with a name something like Space1 and Space2 then in the View Tab use the Save Workspace (You give this a unique name so when you open you only open the workspace and all the other books open). You get a diferrent file extension of XLW, then close each workbook down. Then do an Open of the Workspace and see what happens.

If I am correct when you use something like the Navigation side with Hyperlinks you could then see the individual sheets in the second workbook. A few adjustments to some of the Workbook Options and you have what you want.

 

[reaktorblue]

Its worth a shot. Thanks for the tip. I'm going to need a nap of pulling out my hair all night looking for a solution lol. I'll give this a shot and post back what happens. Thanks again!