Excel is driving me insane. Help?!

LostAndAshamed

I'm trying to collate my monthly reports into one workbook.

I've created a summary page, with links to the more detailed analysis, but the hyperlinks break if I hide the sheets.

I think I need some code to make this work, but I've copied and pasted a few from the internet, and it's not working.

Could someone walk me through it? The Summary Tab is called Summary, and then there is Tab One, Tab Two, and so on. I'm using Excel 2011 on a Mac.

You would make me the happiest spreadsheet user today if you can help! :rotfl:

rmg1

As far as I know, you can't actively link (using hyperlinks) to a hidden sheet. It's basically got nowehere to go.

The best thing to do would be to record a macro (not sure how to do this on a Mac, I'm used to Windows) and then look at the code.

You can then use this code as a base and attach it to a button to unhide the sheet as necessary.

Mister_aaa

Macro button with something like

Sub Button1_Click()

Sheets("Sheet2").Visible = True
Sheets("Sheet2").Select
Range("A1").Select

End Sub

LostAndAshamed

Mister_aaa wrote: »

Macro button with something like

Sub Button1_Click()

Sheets("Sheet2").Visible = True
Sheets("Sheet2").Select
Range("A1").Select

End Sub

This works wonderfully, until I hide the sheet. Then I get runtime error 1004, and it highlights the Sheets.("Sheet2").Select.

I just can't get my head around this!

Mr_Toad

There should be an option to do this.

If you want to use data in hidden sheets to plot a graph then you have to go to the the tools menu and click Options.

Then on the chart tab under active chart called something like 'plot visible cells only' You need to uncheck this box.

I imagine that there's something similar for data but I haven't used it. I'll have a look when I get chance.

Mr_Toad

Been having a think about this and it depends where you are doing you summary calculations.

If you are doing the summary calculations on the hidden sheets then I'm not sure if you can do what you want or not.

However, If, for example, you have three worksheets with the summary on sheet one and the detailed data on sheets two and three then you start with all the sheets visible.

On sheet one you create the summary data by using the data on sheets 2 and 3, for example you might use VLOOKUP to access that data. Once the summary data is correct you can hide sheets 2 and 3 and the VLOOKUP will still work.

If the user wants to they can simply unhide the hidden sheets, if you want to really hide them then you need a macro or some VBA

Mister_aaa

LostAndAshamed wrote: »

This works wonderfully, until I hide the sheet. Then I get runtime error 1004, and it highlights the Sheets.("Sheet2").Select.

I just can't get my head around this!

How are you hiding the sheet?

Finknottle

Excel won't willingly support what you want to do.

The purpose of hiding Excel sheets is to prevent others from viewing them, or even realizing that they exist.

So hyperlinks are disabled to prevent "peeking" at hidden data.

If you're just hiding sheets from yourself for neatness, you might consider a slightly different design.

If you kept your detailed analysis in separate workbook(s) in the same directory, you could open an analysis workbook when you need to, by clicking on a hyperlink.

Otherwise the analysis workbook would stay closed and therefore not visible.

The data in the analysis workbook(s) can still feed the summary workbook using standard formulas.

Doing it this way may or may not suit you, but has the advantage that it avoids maintaining macros.

andydiysaver

I'm an excel developer


sheets have three states of property visible, normal, hidden and very hidden - the ability of communication between normal sheets and these decreases the further to the right of that list you go


so for example, a trick I use in the applications I write is to use code similar to what I see above here to move a sheet temporarily from very hidden to hidden - you can do most operations between a hidden and a visible sheet especially if you use VBA, but very hidden obscures it far further and a whole host of things that would work now doesn't unless you change that property


this code from above is a one way switch


Sub Button1_Click()

Sheets("Sheet2").Visible = True
Sheets("Sheet2").Select
Range("A1").Select

End Sub

ideally you want to make it visible then invisible


better


Sub Button1_Click()

Sheets("Sheet2").Visible = True


'all operations involving pulling data from Sheet2 to elsewhere in program go here




Sheets("Sheet2").Visible = False


'hide again until next time


End Sub




or set a Boolean variable to store the status of sheet


Public IsSheet2Hidden as Boolean
Set IsSheet2Hidden = False


Then


Sub BlahBlahBlahSwitchOnStatus()
' is it already hidden or not if not hidden hide it?
If Is Sheet2Hidden = False Then
Sheets("Sheet2").Visible = False
IsSheet2Hidden = True
End If

If IsSheet2Hidden = True Then
' is it already hidden, unhide it store that info!
Sheets("Sheet2").Visible = True
IsSheet2HIdden = False
End If
End Sub


Sub InfoPass()
'information operations here
If IsSheet2Hidden = True Goto Out
'otherwise all ops here




'blah
'blah blah




Out:
End Sub