need help with microsoft excel dashboard

akashpohal 

If you want to hide the sheets 2-11 while still maintaining the functionality of the hyperlinks, you can use a VBA macro to achieve this.

Here is an example of how you can implement it:

1. Open the Excel workbook and press ALT+F11 to open the Visual Basic Editor.
2. Insert a new module by clicking on "Insert" and selecting "Module."
3. In the module window, paste the following VBA code:

Vba code (untested):

Sub HideSheetsAndSetHyperlinks()
    Dim ws As Worksheet
    
    ' Array of sheet names to hide
    Dim hiddenSheets As Variant
    hiddenSheets = Array("Emirates", "TA", "AMEX", "Spotify", "SA", "McCamm", "Honda", "Energizer", "Accenture-EMEA-and-NA", "Accenture-APAC")
    
    ' Hide the specified sheets
    For Each ws In ThisWorkbook.Sheets
        If IsInArray(ws.Name, hiddenSheets) Then
            ws.Visible = xlSheetHidden
        End If
    Next ws
    
    ' Set the hyperlinks in the QA Dashboard sheet
    Dim dashboardSheet As Worksheet
    Set dashboardSheet = ThisWorkbook.Sheets("QA Dashboard")
    
    ' Update the address of the hyperlinks to use the sheet codenames
    Dim blockNumber As Integer
    For blockNumber = 2 To 11
        Dim hyperlinkCell As Range
        Set hyperlinkCell = dashboardSheet.Range("A" & blockNumber)
        hyperlinkCell.Hyperlinks.Add Anchor:=hyperlinkCell, Address:="", SubAddress:="'" & Sheets(blockNumber).CodeName & "'!A1", TextToDisplay:=hyperlinkCell.Value
    Next blockNumber
End Sub

Function IsInArray(value As Variant, arr As Variant) As Boolean
    IsInArray = (UBound(Filter(arr, value)) > -1)
End Function

1. Modify the hiddenSheets array in the code to include the names of the sheets you want to hide.
2. Close the Visual Basic Editor.
3. Press ALT+F8 to open the "Macro" dialog box.
4. Select the "HideSheetsAndSetHyperlinks" macro and click "Run" to execute the code.

This VBA macro will hide the specified sheets while preserving the functionality of the hyperlinks in the "QA Dashboard" sheet. It also updates the hyperlinks to use the sheet codenames instead of the sheet names, ensuring that the hyperlinks will still work even if the sheet names are hidden.

Remember to save your Excel workbook in a macro-enabled format (e.g., .xlsm) to retain the VBA code. The text, steps and functions were created with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.