Tracking multiple versions - is this possible?

[editorgrl]

Hello! I realize this may be more than Excel is designed for, but maybe not!

I work for a publishing company. We produce 20+ magazines each month, each headed by a different editor.
We want to track each of the magazine's content as it moves through the system.
For instance, a tracking page would have "First Edit," "To Copydesk" "To Design," etc.
And that works just fine if we have one sheet per magazine.

However, some of the editors track content months in advance. Is there a way to do a "subsheet" below each sheet, so that the editor could select whatever month they are working on?
We are trying to avoid having 20 separate Excel files and put them all into one.

Thanks for any advice you have!

 

[EFANYoutube]

Hey editorgrl
I wonder if you would be interested in haveing hidden cells that expand and collapse automatically?
It's a bit hard as I can't vision your current spreadsheet
There shouldnt be an issue with rows as your version should have 1 million rows.
Just need a snippit to see what you want exactly

 

[Logit]

We are trying to avoid having 20 separate Excel files and put them all into one.

Could you use a separate Sheet for each month ? They would all be in a single workbook and easy to keep track of.

 

[editorgrl]

Hey editorgrl
I wonder if you would be interested in haveing hidden cells that expand and collapse automatically?
It's a bit hard as I can't vision your current spreadsheet
There shouldnt be an issue with rows as your version should have 1 million rows.
Just need a snippit to see what you want exactly

Good morning! I don't know anything about hidden cells, I will have to dig into that, thanks!

 

[editorgrl]

Could you use a separate Sheet for each month ? They would all be in a single workbook and easy to keep track of.

It's a good idea, but each publication gets a sheet, and then if there were individual ones for each month, it would get unwieldy. Thanks!

 

[editorgrl]

What I need (and don't think is possible) is pull down menus for each sheet. For instance, the sheet named Magazine 1; click on it, a pull down menu would come up with a sheet for each month of the year and would flip over to that.

 

[Logit]

Well ... as a start ... this would go in the ThisWorkbook module :

Option Explicit

Private Sub Workbook_Open()
    Dim sh As Worksheet

    For Each sh In ActiveWorkbook.Worksheets

        Sheet1.ComboBox1.AddItem sh.Name

    Next sh
End Sub

And this would go in the Sheet "Magazine" sheet level module :

Option Explicit

Private Sub ComboBox1_Change()

    ActiveWorkbook.Worksheets(Me.ComboBox1.Value).Activate

End Sub

Paste a 'ComboBox' on Sheet "Magazine"

 

[EFANYoutube]

I understand what your asking now
There are several ways to do this, one would bew have one sheet set up as a "menu" and set up comboboxes and Logit suggested
The other would be to create a popup form that would dynamically create the links and once clicked the form would close and the sheet they wanted would become visable