A Formating Question ??

P

Pheno

New member
Joined
Jul 28, 2016
Messages
8
Reaction score
0
Points
0
Location
Southern California
Let me begin by saying hello to all, and that I am happy this site exists. I hope to learn a lot here as I am a beginner when it comes to Excel. So, to my first question.

I have a spreadsheet being used for RASCI charting. The first cell is locked as the department titles are list from rows B to S. I have then stared a new chart below that uses different department titles. As you scroll down the second chart titles are lost behind the ones from the chart above. My question is if there is a way for the titles from the second chart to take the place of the titles of the first chart as you scroll down so this pertinent information is still viewable?
 
Pheno said:
I have a spreadsheet being used for RASCI charting. ............ My question is if there is a way for the titles from the second chart to take the place of the titles of the first chart as you scroll down so this pertinent information is still viewable?
Click to expand...
No one here is not clairvoyant magician. Your example file with the expected results, the good would come?
 
I am not sure what you are asking, but I think you are saying that a copy of the sheet would be helpful? Attached.
 

Attachments

  • RACI Template.xlsx
    31.1 KB · Views: 8
No worries, when I saw that you were from Croatia I sort of figured things out. While I appreciate your looking at this, I really do want to learn what needs to be done to make the sheet do what I want it too. I am too long in using Excel to not have learned this years ago, so now I figure its time to crash myself into how to use Excel better.
 
Pheno said:
No worries,
Click to expand...
Maybe I is not much help, but that's at least some idea.
If I understood you well.
I think you can do it, so that in the second row put a formula that will display the second row titles.
regards
 

Attachments

  • RACI Template.xlsx
    31.3 KB · Views: 8
Hide or unhide Rows via VBA

New idea with shortcut via VBA. regards
 

Attachments

  • pheno-navic2.xlsm
    37 KB · Views: 13
Hmm, not sure what the issue is here. It wants to save as a php extension. When renamed it becomes un-useable. What am I doing wrong?
 
Hide or unhide rows by VBA via shortcut

Pheno said:
Hmm, not sure what the issue is here. It wants to save as a php extension. When renamed it becomes un-useable. What am I doing wrong?
Click to expand...
I do not know why you've got problems.
Here is a new file format
btw:
Click the right mouse on the name of the tab sheet => View Code => Paste

Code: 
Option Explicit

Sub HideRows()
'set shortcut Ctrl+Shift+H
Rows("1:33").EntireRow.Hidden = True
End Sub

Sub UnhideRows()
'set shortcut Ctrl+Shift+U
Rows("1:33").EntireRow.Hidden = False
End Sub
 

Attachments

  • pheno-navic2.xls
    73.5 KB · Views: 7
  • pheno-navic2.xlsb
    29.2 KB · Views: 9
So I got the scripts in place as you posted them. And they had the effect of showing but not doing what I had hoped they would. When you scroll down I would like the second header that comes up, to replace the top header.
 
Pheno said:
When you scroll down I would like the second header that comes up, to replace the top header.
Click to expand...
I'm sorry, I'm no longer able to help.
I hope that someone else will try to help you.
Good luck.
 
Hi @Pheno
I have a new idea if it suits you.

You can create a new sheet "helper".
On it put two headers.
Using VBA shortcut, you can switch the headers.

This VBA code put in VBE Module1
Code: 
Sub CopyFirstHader()
'shortcut Ctrl+Shift+K
Application.ScreenUpdating = False
    Sheets("helper").Select
    Range("A1:P1").Select
    Selection.Copy
    Sheets("RACI Template").Select
    Range("A1").Select
    ActiveSheet.Paste
    Range("A4").Select
Application.ScreenUpdating = True
End Sub

Sub CopySecondHeader()
'shortcut Ctrl+Shift+L
Application.ScreenUpdating = False
    Sheets("helper").Select
    Range("A2:P2").Select
    Selection.Copy
    Sheets("RACI Template").Select
    Range("A1").Select
    ActiveSheet.Paste
    Range("A4").Select
Application.ScreenUpdating = True
End Sub
Set shortcut key for each VBA procedure.

See one of the attachment file
 

Attachments

  • pheno-navic3.xls
    81 KB · Views: 3
  • pheno-navic3.xlsm
    39.4 KB · Views: 7
  • pheno-navic3.xlsb
    32 KB · Views: 8
Change header of first row based on selected row

Hi @Pheno
Here's a new idea. :)
Excel automatically changes the header based on the selected row
 

Attachments

  • pheno-navic4.xls
    85.5 KB · Views: 5
  • pheno-navic4.xlsm
    40.7 KB · Views: 9
Pheno said:
Can you post the code please, every time I try and download the file it wants to be saved as a .php
Click to expand...
here is :)
Code: 
Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
            
            If ActiveCell.Row < 35 Then
                Worksheets("helper").Range("A1:P1").Copy Worksheets("RACI Template").Range("A1:P1")
                Application.CutCopyMode = False
            End If
            
            If ActiveCell.Row >= 35 Then
                Worksheets("helper").Range("A2:P2").Copy Worksheets("RACI Template").Range("A1:P1")
                Application.CutCopyMode = False
            End If
End Sub
 
I think I am doing something wrong. I have right clicked on the tab at the bottom, selected "view code", copied the script into the window, saved the document, reopened and nothing happens. I have saved it as a macro-enabled file as prompted so I am not sure if these are the proper steps?
 
Automatic change or display headers based on the selected row

Pheno said:
I think I am doing something wrong.
Click to expand...
What more can I say.
Here are screenshots, probably will be clearer

'helper' sheet. There is two headers (blue and green)



'RACI Template' sheet. Selected cell A12 in Row12. Visible is the "blue" header.
Therefore, the selected row is less than 35



'RACI Template' sheet. Selected cell A44 in Row44. Visible is the "green" header
Therefore, the selected row is greater than or equal to 35



Excel VBE window. Module sheet "Sheet1(RACI Template)"



Good luck.

btw: Format file need be *.xls or *.xlsm
 
Well, I have tried to implement this and I cant figure out the helper sheet. I also can not download the file you upload as it says its a Excel document but will only download in as a .php, and I have no idea what to do with that. I have tried from three different systems and it is not a .xls or .xlsm so I thank you for your efforts, but I cant seem to make any of them work.
 
You must log in or register to reply here.
Back
Top