Automatically Have a Cell Reference a Cell When a New Sheet is Created

[CtrlAltRage]

Hey all,

I imagine there's an easy way to do this but I'm drawing a blank.

I have a sheet that will list projects I am working on.

On the first sheet (PROJECTS) when I enter in a project name (Column A), I've gotten it so that it automatically copies a sheet (Project Template) and creates a new sheet with the name of the sheet being the value of the project name I put in Column A.

What I'm trying to do is have it so that when the sheet is created, the first sheet (PROJECTS) automatically references specific cells in the newly created sheet in the row that the cell was updated. And when I add another project, creating a new sheet, I'd like that row to do the same, and so on.

To summarize:

- PROJECTS Sheet: I enter in "Project 1" under Column A/Project Name.

- The Project Template sheet gets copied and the name of the new sheet is now "Project 1"

- In the Project 1 sheet - A1 also has the name "Project 1"

---The above I have already gotten to work---

Then in the first sheet (PROJECTS), the row Project 1 was entered pulls the data from the newly created sheet under the "% Completed" and "Due Date" cells the respective cells under the PROJECTS sheet.

I hope that made sense - I've attached a file for reference. I am guessing as soon as I see the solution I'm going to facepalm at how easy it was.

Here is the first sheet (PROJECTS)

Project List Sheet.xlsm
	A	B	C	D	E	F
1	PROJECT LIST
2	PROJECT NAME	DATE ENTERED	DUE DATE	% COMPLETE	PRIORITY	NOTES
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
PROJECTS

Here is the second sheet (Project Template)

Project List Sheet.xlsm
	A	B	C	D	E	F	G	H
1	Project Name		Due Date:	1/1/2099
2
3
4	Tasks	Notes	Priority	Completed		Status	Priority	Grade
5						ü	High	5
6							Medium	3
7							Low	1
8
9
10						Total Tasks	0
11						Completed Task Score	0
12						Total Task Score	0
13						% Completed	0%
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
Project Template

Cell Formulas
Range	Formula
G10	G10	=COUNTIF($A$5:$A$29,"<>")
G11	G11	=COUNTIFS($C$5:$C$29,$G$5,$D$5:$D$29,$F$5)*$H$5+COUNTIFS($C$5:$C$29,$G$6,$D$5:$D$29,$F$5)*$H$6+COUNTIFS($C$5:$C$29,$G$7,$D$5:$D$29,$F$5)*$H$7
G12	G12	=COUNTIF($C$5:$C$29,$G$5)*$H$5+COUNTIF($C$5:$C$29,$G$6)*$H$6+COUNTIF($C$5:$C$29,$G$7)*$H$7
G13	G13	=IFERROR(G11/G12,0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A5:D29	Expression	=AND($D5=$F$5,$A5<>"")	text	NO

Cells with Data Validation
Cell	Allow	Criteria
C5:C29	List	=$G$5:$G$8

Thanks in advance!

 

[bebo021999]

Not sure 100%, but this code trigger any change/ update in column A of sheet PROJECTS, dupplicate sheet template, then create formula in sheet PROJECTS to get value from new sheet.
Right click on sheet name, View Code then paste below code into:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns(1)) Is Nothing Or Target.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Evaluate("=ISREF(" & Target.Value & "!A1)") Then
    Sheets("Project Template").Copy after:=Sheets(Sheets.Count)
    ActiveSheet.Name = Target.Value
End If
Me.Activate
Target.Offset(, 2).Formula = "=" & Target.Value & "!D1"
Target.Offset(, 3).Formula = "=" & Target.Value & "!G13"
End Sub

 

[CtrlAltRage]

Not sure 100%, but this code trigger any change/ update in column A of sheet PROJECTS, dupplicate sheet template, then create formula in sheet PROJECTS to get value from new sheet.
Right click on sheet name, View Code then paste below code into:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns(1)) Is Nothing Or Target.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Evaluate("=ISREF(" & Target.Value & "!A1)") Then
    Sheets("Project Template").Copy after:=Sheets(Sheets.Count)
    ActiveSheet.Name = Target.Value
End If
Me.Activate
Target.Offset(, 2).Formula = "=" & Target.Value & "!D1"
Target.Offset(, 3).Formula = "=" & Target.Value & "!G13"
End Sub

That worked wonderfully, thank you!

Now, if you don't mind me asking - if I wanted to take the names of the projects that the sheets were named after and turn them into links to their respective sheets, where would I place that code?

 

[bebo021999]

Add one more selection change event to trigger column A select (instead of add hyperlink into cell)

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns(1)) Is Nothing Or Target.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Evaluate("=ISREF(" & Target.Value & "!A1)") Then
    Sheets("Project Template").Copy after:=Sheets(Sheets.Count)
    ActiveSheet.Name = Target.Value
End If
Me.Activate
Target.Offset(, 2).Formula = "=" & Target.Value & "!D1"
Target.Offset(, 3).Formula = "=" & Target.Value & "!G13"
End Sub

'ADDED
'--------------------------------------------------------------------
Private Sub Worksheet_selectionChange(ByVal Target As Range)
If Intersect(Target, Columns(1)) Is Nothing Or Target.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Evaluate("=ISREF(" & Target.Value & "!A1)") Then Exit Sub
Sheets(Target.Value).Activate
End Sub

 

[CtrlAltRage]

Add one more selection change event to trigger column A select (instead of add hyperlink into cell)

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns(1)) Is Nothing Or Target.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Evaluate("=ISREF(" & Target.Value & "!A1)") Then
    Sheets("Project Template").Copy after:=Sheets(Sheets.Count)
    ActiveSheet.Name = Target.Value
End If
Me.Activate
Target.Offset(, 2).Formula = "=" & Target.Value & "!D1"
Target.Offset(, 3).Formula = "=" & Target.Value & "!G13"
End Sub

'ADDED
'--------------------------------------------------------------------
Private Sub Worksheet_selectionChange(ByVal Target As Range)
If Intersect(Target, Columns(1)) Is Nothing Or Target.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Evaluate("=ISREF(" & Target.Value & "!A1)") Then Exit Sub
Sheets(Target.Value).Activate
End Sub

Thanks again! For some reason when I apply that last bit of code when I enter in a new task in the PROJECTS tab it asks me to open a file.

 

[bebo021999]

Try to attach a screenshot with the pop up message asking to open a file.

 

[CtrlAltRage]

Try to attach a screenshot with the pop up message asking to open a file.

Here you go:

 

[CtrlAltRage]

UPDATE:

It seems to only happen when I have spaces in the project name I enter in.

 

[CtrlAltRage]

UPDATE:

It seems to only happen when I have spaces in the project name I enter in.

The spreadsheet works great and I appreciate the help! Is there anyway I can fix it so that the Update Values Prompt doesn't show up when I enter in a task with spaces in it?