Linking to a workbook that doesn't yet exist - for use in template creating

[SophieYaldwyn]

Hi all,
I am trying to link separate workbooks-not-worksheets that are yet to be created - these would be workbooks created in the future using templates, and the destination workbook I am working in will be a master template as well. This is for a project for study and I have really hit a roadblock here and perhaps thinking I need to completely re-think the entire first workbook I have created. For full information, the study question reads:

"Create a series of Excel spreadsheets to demonstrate the skills you learned in this module.

You will need to demonstrate your ability to design a group of spreadsheets that:

• have a link between one spreadsheet and another
• demonstrates conditional formatting
• use at least three functions from Excel and one formula you create
• has at least one macro
• uses a template you created to produce new workbooks
• uses charts to help analyse data

You will need to provide printouts from the spreadsheets and a copy of a chart.

Example:
A friend knows you have a good understanding of Excel.

He would like you to help him with his business. He wants to keep a spreadsheet for each month’s sales. He has five sales staff and would like to record the daily sales figure. He wants to see a monthly total for each person and a daily sales total. He would also like an average daily sale, the lowest sales figure, and the highest sales figure for each day. The data should have highlighting to show figures below the daily average and figures above the average. There should be a separate worksheet that shows a commission figure for each salesperson daily. He pays each person a flat 15% of everything they sell as a commission.

He thinks a template for the above spreadsheet would help him create a new spreadsheet every month.

Then he would like a master spreadsheet that shows each person’s monthly sales figure. He would like to have an annual total for each and a grand total. He would like a chart showing each person by month and another chart that shows each person’s annual sales figure as a part of the total sales figure."

Is what I am trying to achieve something that is possible?
I have been trying to have a reference cell in the master workbook that shows the workbook-to-be-referenced file name that can then be referenced in the formulae throughout the workbook. So as the new workbooks are created, that reference cell can be edited to contain the new workbook name, which would then automatically adjust content in all the formulae throughout the workbook to create the correct path.
I keep coming up with a File Name Syntax error though when trying to reference that reference cell (I3) in creating the new formula rather than clicking on a cell in the separate workbook.
Hopefully this makes sense and someone is able to lend some expertise. I have attached all workbooks and worksheets below for reference and a screenshot of the error.
Thankyou!
Sophie

Workbook 1, sheet 1:

ProjectBSBTEC402P1.xlsm
	A	B	C	D	E	F	G	H	I
1	Monthly Sales Figures: Business Name
2	Month	2				Above Average	Below Average	Highest Figure	Lowest Figure
3	Year	2022			Legend:
4	Leap Year?	No
5	Date	Day	Person 1	Person 2	Person 3	Person 4	Person 5	Average Daily Sale Total	Overall Daily Sales Total
6	1	Tue	$ 123.00					$ 123.00	$ 123.00
7	2	Wed	$ 798.00					$ 798.00	$ 798.00
8	3	Thu	$ 23,545.00	$ 7,856.00				$ 15,700.50	$ 31,401.00
9	4	Fri	$ 987.00					$ 987.00	$ 987.00
10	5	Sat	$ 3,453.00	$ 5,668.00				$ 4,560.50	$ 9,121.00
11	6	Sun		$ 3,246.00				$ 3,246.00	$ 3,246.00
12	7	Mon
13	8	Tue
14	9	Wed
15	10	Thu
16	11	Fri
17	12	Sat
18	13	Sun
19	14	Mon
20	15	Tue
21	16	Wed
22	17	Thu
23	18	Fri
24	19	Sat
25	20	Sun
26	21	Mon
27	22	Tue
28	23	Wed
29	24	Thu
30	25	Fri
31	26	Sat
32	27	Sun
33	28	Mon
34		#VALUE!
35		#VALUE!
36		#VALUE!
37		Monthly Total	$ 28,906.00	$ 16,770.00					$ 45,676.00
38					Overall Daily Sale Average			$ 4,235.83
MonthlySalesFigures

Cell Formulas
Range	Formula
B4	B4	=IF(MONTH(DATE(B3,2,29))=2,"Yes","No")
H6:H20,H25:H36	H6	=IFERROR(AVERAGE(C6:G6),"")
B6:B36	B6	=DATE($B$3,$B$2,$A6)
A34	A34	=IF(OR(B2=1,B2=3,B2=4,B2=5,B2=6,B2=7,B2=8,B2=9,B2=10,B2=11,B2=12,B4="Yes"), "29", "")
A35	A35	=IF(B2=2,"","30")
A36	A36	=IF(OR(B2=1,B2=3,B2=5,B2=7,B2=8,B2=10,B2=12),"31","")
C37:G37,I37	C37	=IF(SUM(C6:C36)=0,"",SUM(C6:C36))
I6:I36	I6	=IF(SUM(C6:G6)=0,"",SUM(C6:G6))
H38	H38	=IFERROR(AVERAGEIF(H6:H36,"<>-"),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C36:G36	Cell Value	top 1 bottom values	text	NO
C36:G36	Cell Value	top 1 values	text	NO
C36:G36	Cell Value	below average	text	NO
C36:G36	Cell Value	above average	text	NO
C35:G35	Cell Value	top 1 bottom values	text	NO
C35:G35	Cell Value	top 1 values	text	NO
C35:G35	Cell Value	below average	text	NO
C35:G35	Cell Value	above average	text	NO
C34:G34	Cell Value	top 1 bottom values	text	NO
C34:G34	Cell Value	top 1 values	text	NO
C34:G34	Cell Value	below average	text	NO
C34:G34	Cell Value	above average	text	NO
C33:G33	Cell Value	top 1 bottom values	text	NO
C33:G33	Cell Value	top 1 values	text	NO
C33:G33	Cell Value	below average	text	NO
C33:G33	Cell Value	above average	text	NO
C32:G32	Cell Value	top 1 bottom values	text	NO
C32:G32	Cell Value	top 1 values	text	NO
C32:G32	Cell Value	below average	text	NO
C32:G32	Cell Value	above average	text	NO
C31:G31	Cell Value	top 1 bottom values	text	NO
C31:G31	Cell Value	top 1 values	text	NO
C31:G31	Cell Value	below average	text	NO
C31:G31	Cell Value	above average	text	NO
C30:G30	Cell Value	top 1 bottom values	text	NO
C30:G30	Cell Value	top 1 values	text	NO
C30:G30	Cell Value	below average	text	NO
C30:G30	Cell Value	above average	text	NO
C29:G29	Cell Value	top 1 bottom values	text	NO
C29:G29	Cell Value	top 1 values	text	NO
C29:G29	Cell Value	below average	text	NO
C29:G29	Cell Value	above average	text	NO
C28:G28	Cell Value	top 1 bottom values	text	NO
C28:G28	Cell Value	top 1 values	text	NO
C28:G28	Cell Value	below average	text	NO
C28:G28	Cell Value	above average	text	NO
C27:G27	Cell Value	top 1 bottom values	text	NO
C27:G27	Cell Value	top 1 values	text	NO
C27:G27	Cell Value	below average	text	NO
C27:G27	Cell Value	above average	text	NO
C25:G25	Cell Value	top 1 bottom values	text	NO
C25:G25	Cell Value	top 1 values	text	NO
C25:G25	Cell Value	below average	text	NO
C25:G25	Cell Value	above average	text	NO
C23:G23	Cell Value	top 1 bottom values	text	NO
C23:G23	Cell Value	top 1 values	text	NO
C23:G23	Cell Value	below average	text	NO
C23:G23	Cell Value	above average	text	NO
C24:G24	Cell Value	top 1 bottom values	text	NO
C24:G24	Cell Value	top 1 values	text	NO
C24:G24	Cell Value	below average	text	NO
C24:G24	Cell Value	above average	text	NO
C26:G26	Cell Value	top 1 bottom values	text	NO
C26:G26	Cell Value	top 1 values	text	NO
C26:G26	Cell Value	below average	text	NO
C26:G26	Cell Value	above average	text	NO
C22:G22	Cell Value	top 1 bottom values	text	NO
C22:G22	Cell Value	top 1 values	text	NO
C22:G22	Cell Value	below average	text	NO
C22:G22	Cell Value	above average	text	NO
C21:G21	Cell Value	top 1 bottom values	text	NO
C21:G21	Cell Value	top 1 values	text	NO
C21:G21	Cell Value	below average	text	NO
C21:G21	Cell Value	above average	text	NO
C20:G20	Cell Value	top 1 bottom values	text	NO
C20:G20	Cell Value	top 1 values	text	NO
C20:G20	Cell Value	below average	text	NO
C20:G20	Cell Value	above average	text	NO
C19:G19	Cell Value	top 1 bottom values	text	NO
C19:G19	Cell Value	top 1 values	text	NO
C19:G19	Cell Value	below average	text	NO
C19:G19	Cell Value	above average	text	NO
C18:G18	Cell Value	top 1 bottom values	text	NO
C18:G18	Cell Value	top 1 values	text	NO
C18:G18	Cell Value	below average	text	NO
C18:G18	Cell Value	above average	text	NO
C17:G17	Cell Value	top 1 bottom values	text	NO
C17:G17	Cell Value	top 1 values	text	NO
C17:G17	Cell Value	below average	text	NO
C17:G17	Cell Value	above average	text	NO
C16:G16	Cell Value	top 1 bottom values	text	NO
C16:G16	Cell Value	top 1 values	text	NO
C16:G16	Cell Value	below average	text	NO
C16:G16	Cell Value	above average	text	NO
C15:G15	Cell Value	top 1 bottom values	text	NO
C15:G15	Cell Value	top 1 values	text	NO
C15:G15	Cell Value	below average	text	NO
C15:G15	Cell Value	above average	text	NO
C14:G14	Cell Value	top 1 bottom values	text	NO
C14:G14	Cell Value	top 1 values	text	NO
C14:G14	Cell Value	below average	text	NO
C14:G14	Cell Value	above average	text	NO
C13:G13	Cell Value	top 1 bottom values	text	NO
C13:G13	Cell Value	top 1 values	text	NO
C13:G13	Cell Value	below average	text	NO
C13:G13	Cell Value	above average	text	NO
C10:G10	Cell Value	top 1 bottom values	text	NO
C10:G10	Cell Value	top 1 values	text	NO
C10:G10	Cell Value	below average	text	NO
C10:G10	Cell Value	above average	text	NO
C8:G8	Cell Value	top 1 bottom values	text	NO
C8:G8	Cell Value	top 1 values	text	NO
C8:G8	Cell Value	below average	text	NO
C8:G8	Cell Value	above average	text	NO
C9:G9	Cell Value	top 1 bottom values	text	NO
C9:G9	Cell Value	top 1 values	text	NO
C9:G9	Cell Value	below average	text	NO
C9:G9	Cell Value	above average	text	NO
C12:G12	Cell Value	top 1 bottom values	text	NO
C12:G12	Cell Value	top 1 values	text	NO
C12:G12	Cell Value	below average	text	NO
C12:G12	Cell Value	above average	text	NO
C11:G11	Cell Value	top 1 bottom values	text	NO
C11:G11	Cell Value	top 1 values	text	NO
C11:G11	Cell Value	below average	text	NO
C11:G11	Cell Value	above average	text	NO
C7:G7	Cell Value	top 1 bottom values	text	NO
C7:G7	Cell Value	top 1 values	text	NO
C6:G6	Cell Value	top 1 bottom values	text	NO
C6:G6	Cell Value	top 1 values	text	NO
C7:G7	Cell Value	below average	text	NO
C7:G7	Cell Value	above average	text	NO
C6:G6	Cell Value	above average	text	NO
C6:G6	Cell Value	below average	text	NO
A34	Cell	contains an error	text	NO
B34:B36	Cell	contains an error	text	NO

Cells with Data Validation
Cell	Allow	Criteria
B2	List	1,2,3,4,5,6,7,8,9,10,11,12
B3	List	2022,2023,2024,2025,2026,2027,2028,2029,2030,2031,2032,2033,2034,2035,2036,2037,2038,2039,2040,2041,2042,2043,2044,2045,2046,2047,2048,2049,2050,2051,2052,2053,2054,2055,2056,2057,2058,2059,2060,2061,2062,2063,2064,2065,2066,2067,2068,2069,2070,2071,2072

Workbook 1 Sheet 2:

ProjectBSBTEC402P1.xlsm
	A	B	C	D	E	F	G
1	Monthly Commission Figures: Business Name
2	Month	2
3	Year	2022			Commission Rate:		15%
4	Leap Year?	No
5	Date	Day	Person 1	Person 2	Person 3	Person 4	Person 5
6	1	Tue	$ 18.45
7	2	Wed	$ 119.70
8	3	Thu	$3,531.75	$1,178.40
9	4	Fri	$ 148.05
10	5	Sat	$ 517.95	$ 850.20
11	6	Sun		$ 486.90
12	7	Mon
13	8	Tue
14	9	Wed
15	10	Thu
16	11	Fri
17	12	Sat
18	13	Sun
19	14	Mon
20	15	Tue
21	16	Wed
22	17	Thu
23	18	Fri
24	19	Sat
25	20	Sun
26	21	Mon
27	22	Tue
28	23	Wed
29	24	Thu
30	25	Fri
31	26	Sat
32	27	Sun
33	28	Mon
34		#VALUE!
35		#VALUE!
36		#VALUE!
37		Monthly Total	$4,335.90	$2,515.50
CommissionCalculations

Cell Formulas
Range	Formula
B2:B3	B2	=MonthlySalesFigures!B2
B4	B4	=IF(MONTH(DATE(B3,2,29))=2,"Yes","No")
C6:G36	C6	=IF(MonthlySalesFigures!C6*$G$3=0,"",MonthlySalesFigures!C6*$G$3)
C37:G37	C37	=IF(SUM(C6:C36)=0,"",SUM(C6:C36))
B6:B36	B6	=DATE($B$3,$B$2,$A6)
A34	A34	=IF(OR(B2=1,B2=3,B2=4,B2=5,B2=6,B2=7,B2=8,B2=9,B2=10,B2=11,B2=12,B4="Yes"), "29", "")
A35	A35	=IF(B2=2,"","30")
A36	A36	=IF(OR(B2=1,B2=3,B2=5,B2=7,B2=8,B2=10,B2=12),"31","")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A34	Cell	contains an error	text	NO
B34:B36	Cell	contains an error	text	NO

Cells with Data Validation
Cell	Allow	Criteria
B2	List	1,2,3,4,5,6,7,8,9,10,11,12
B3	List	2022,2023,2024,2025,2026,2027,2028,2029,2030,2031,2032,2033,2034,2035,2036,2037,2038,2039,2040,2041,2042,2043,2044,2045,2046,2047,2048,2049,2050,2051,2052,2053,2054,2055,2056,2057,2058,2059,2060,2061,2062,2063,2064,2065,2066,2067,2068,2069,2070,2071,2072

Workbook 2, Sheet 1 - Where I am trying to create the link using cell I3 as a reference within a cell link such as cell B5 but not manually input.

Project BSBTEC402 P21.xlsx
	A	B	C	D	E	F	G	H	I
1	Individual Sales Figure Totals
2	Year
3								File name of source workbook	ProjectBSBTEC402P1.xlsm
4	Month	Person 1	Person 2	Person 3	Person 4	Person 5
5	January	$ 28,906.00
6	February
7	March
8	April
9	May
10	June
11	July
12	August
13	September
14	October
15	November
16	December
17	Annual Total
18	Annual Grand Total
Sheet1

Cell Formulas
Range	Formula
B5	B5	=[ProjectBSBTEC402P1.xlsm]MonthlySalesFigures!$C$37

 

[Worf]

Here are two ways to retrieve data from a closed workbook:

ranges.xlsm
	K	L
6	c:\test\	power query1.xlsm
7	'C:\test\[Power Query1.xlsm]1'
8	!$N10:$O14
9
10	30
11	sundae
Sheet1

Cell Formulas
Range	Formula
L6	L6	="power query1.xlsm"
K6	K6	="c:\test\"
K7	K7	="'C:\test\[Power Query1.xlsm]1'"
K8	K8	="!$N10:$O14"
K10	K10	=VLOOKUP(3,'C:\test\[Power Query1.xlsm]1'!$N10:$O14,2,FALSE)

Public Function getval(path$, file$, sheet$, ref$)
If Right(path, 1) <> "\" Then path = path & "\"
If Dir(path & file) = "" Then
    getval = "File Not Found"
    Exit Function
End If
getval = ExecuteExcel4Macro("'" & path & "[" & file & "]" & sheet & _
"'!" & Range(ref).Range("A1").Address(, , xlR1C1))
End Function

Sub main()

[k10].Formula = "=VLOOKUP(3," & [k7] & [k8] & ",2,FALSE)"
[k11] = getval([k6], [L6], "1", "j9")

End Sub

 

[SophieYaldwyn]

Here are two ways to retrieve data from a closed workbook:

ranges.xlsm
	K	L
6	c:\test\	power query1.xlsm
7	'C:\test\[Power Query1.xlsm]1'
8	!$N10:$O14
9
10	30
11	sundae
Sheet1

Cell Formulas
Range	Formula
L6	L6	="power query1.xlsm"
K6	K6	="c:\test\"
K7	K7	="'C:\test\[Power Query1.xlsm]1'"
K8	K8	="!$N10:$O14"
K10	K10	=VLOOKUP(3,'C:\test\[Power Query1.xlsm]1'!$N10:$O14,2,FALSE)

Public Function getval(path$, file$, sheet$, ref$)
If Right(path, 1) <> "\" Then path = path & "\"
If Dir(path & file) = "" Then
    getval = "File Not Found"
    Exit Function
End If
getval = ExecuteExcel4Macro("'" & path & "[" & file & "]" & sheet & _
"'!" & Range(ref).Range("A1").Address(, , xlR1C1))
End Function

Sub main()

[k10].Formula = "=VLOOKUP(3," & [k7] & [k8] & ",2,FALSE)"
[k11] = getval([k6], [L6], "1", "j9")

End Sub

Hi Worf,
I'm sorry I don't understand how I can use this? Does it also apply to workbooks that don't yet exist? I'm trying to understand the processes and just not understanding.
I've copied into my workbook and tried playing around and subbing in example workbook names to the file name etc but just not figuring it out.
I'm sorry!
Is it possible to explain a bit please?
Thank you,
Sophie

 

[Worf]

worksheet formula to retrieve values from closed workbook

Maybe you will prefer a worksheet function that can be used like any other. Just pay attention on where to paste the code.

This is a dynamic solution that accepts cell values as arguments for the formula.

To insert standard module: Insert > Module

To insert class module: Insert > Class Module

Tell me if you have trouble implementing it.

ranges.xlsm
	C	D
5
6	c:\test\	1
7	power query1.xlsm	J9
8	sundae
9
Sheet1

Cell Formulas
Range	Formula
C6	C6	="c:\test\"
C7	C7	="power query1.xlsm"
C8	C8	=getval(C6,C7,D6,D7)

Rem standard module

Function xlapp()
Static oapp As New Class1
Set xlapp = oapp.xlapp
End Function

Public Function getval(Path$, file$, sheet$, ref$)
If Right(Path, 1) <> "\" Then Path = Path & "\"
If Dir(Path & file) = "" Then
    getval = "File Not Found"
    Exit Function
End If
getval = xlapp.ExecuteExcel4Macro("'" & Path & "[" & file & "]" & sheet & _
"'!" & Range(ref).Range("A1").Address(, , xlR1C1))
End Function

Rem ***********************

Rem class module named Class1

Public xlapp

Private Sub Class_Initialize()
Set xlapp = CreateObject("Excel.Application")
End Sub

Private Sub Class_Terminate()
xlapp.Quit
Set xlapp = Nothing
End Sub

 

[SophieYaldwyn]

worksheet formula to retrieve values from closed workbook

Maybe you will prefer a worksheet function that can be used like any other. Just pay attention on where to paste the code.

This is a dynamic solution that accepts cell values as arguments for the formula.

To insert standard module: Insert > Module

To insert class module: Insert > Class Module

Tell me if you have trouble implementing it.

ranges.xlsm
	C	D
5
6	c:\test\	1
7	power query1.xlsm	J9
8	sundae
9
Sheet1

Cell Formulas
Range	Formula
C6	C6	="c:\test\"
C7	C7	="power query1.xlsm"
C8	C8	=getval(C6,C7,D6,D7)

Rem standard module

Function xlapp()
Static oapp As New Class1
Set xlapp = oapp.xlapp
End Function

Public Function getval(Path$, file$, sheet$, ref$)
If Right(Path, 1) <> "\" Then Path = Path & "\"
If Dir(Path & file) = "" Then
    getval = "File Not Found"
    Exit Function
End If
getval = xlapp.ExecuteExcel4Macro("'" & Path & "[" & file & "]" & sheet & _
"'!" & Range(ref).Range("A1").Address(, , xlR1C1))
End Function

Rem ***********************

Rem class module named Class1

Public xlapp

Private Sub Class_Initialize()
Set xlapp = CreateObject("Excel.Application")
End Sub

Private Sub Class_Terminate()
xlapp.Quit
Set xlapp = Nothing
End Sub

Hi Worf,
I'm sorry for the delayed response, I really appreciate your help a lot!
I am frustrated because I just don't yet have the understanding to be able to implement your suggestions.
I have a friend who is well versed in VBA who I am going to sit down with with your solutions and hopefully then be able to understand and implement one of them.
I will let you know.

Thank you so so much!

Regards,
Sophie

 

[SophieYaldwyn]

worksheet formula to retrieve values from closed workbook

Maybe you will prefer a worksheet function that can be used like any other. Just pay attention on where to paste the code.

This is a dynamic solution that accepts cell values as arguments for the formula.

To insert standard module: Insert > Module

To insert class module: Insert > Class Module

Tell me if you have trouble implementing it.

ranges.xlsm
	C	D
5
6	c:\test\	1
7	power query1.xlsm	J9
8	sundae
9
Sheet1

Cell Formulas
Range	Formula
C6	C6	="c:\test\"
C7	C7	="power query1.xlsm"
C8	C8	=getval(C6,C7,D6,D7)

Rem standard module

Function xlapp()
Static oapp As New Class1
Set xlapp = oapp.xlapp
End Function

Public Function getval(Path$, file$, sheet$, ref$)
If Right(Path, 1) <> "\" Then Path = Path & "\"
If Dir(Path & file) = "" Then
    getval = "File Not Found"
    Exit Function
End If
getval = xlapp.ExecuteExcel4Macro("'" & Path & "[" & file & "]" & sheet & _
"'!" & Range(ref).Range("A1").Address(, , xlR1C1))
End Function

Rem ***********************

Rem class module named Class1

Public xlapp

Private Sub Class_Initialize()
Set xlapp = CreateObject("Excel.Application")
End Sub

Private Sub Class_Terminate()
xlapp.Quit
Set xlapp = Nothing
End Sub

Hi again Worf,
I have been unable to organise a time with my friend to try your VBA solution.
I have been trying copious other possible avenues and thought I finally had something figured out but am now encountering another problem.
I opted to creating an instruction for file name save format in the one workbook so I could create links to the future workbooks in the master with hopes of using the 'find and replace' tool to change the years of the file names when needed - via another instruction I have yet to include in the master workbook. This is the current master that I am trying a few things with:

Project BSBTEC402 P21 to blank for template after perfecting links.xlsm
	B	C	D	E	F	G
3	Individual Sales Figure Totals
4	Year
5
6	Month	Person 1	Person 2	Person 3	Person 4	Person 5
7	January	$ -	$ -	$ -	$ -	$ -
8	February	$ -	$ -	$ -	$ -	$ -
9	March	$ -	$ -	$ -	$ -	$ -
10	April	$ -	$ -	$ -	$ -	$ -
11	May	$ -
12	June	$ -
13	July	$ -
14	August	$ -
15	September	$ -
16	October	$ -
17	November	#REF!
18	December	$ 14,000.00	$ 14,000.00	$ 18,000.00	$ 22,000.00	$ 26,000.00
19	Annual Total	$ 24,000.00	$ 14,000.00	$ 18,000.00	$ 22,000.00	$ 26,000.00
20	Annual Grand Total			$ 104,000.00
Sheet1

Cell Formulas
Range	Formula
D7	D7	=IF(D7='https://qakitchens.sharepoint.com/Shared Documents/QA Kitchens/Documents/Sophie Study/[QAkitchensSalesJanuary2022.xlsm]MonthlySalesFigures'!$D$37=#REF!,"",'https://qakitchens.sharepoint.com/Shared Documents/QA Kitchens/Documents/Sophie Study/[QAkitchensSalesJanuary2022.xlsm]MonthlySalesFigures'!$D$37)
E7	E7	=IF(E7='https://qakitchens.sharepoint.com/Shared Documents/QA Kitchens/Documents/Sophie Study/[QAkitchensSalesJanuary2022.xlsm]MonthlySalesFigures'!$C$37=#REF!,"",'https://qakitchens.sharepoint.com/Shared Documents/QA Kitchens/Documents/Sophie Study/[QAkitchensSalesJanuary2022.xlsm]MonthlySalesFigures'!$E$37)
F7	F7	=IF(F7='https://qakitchens.sharepoint.com/Shared Documents/QA Kitchens/Documents/Sophie Study/[QAkitchensSalesJanuary2022.xlsm]MonthlySalesFigures'!$C$37=#REF!,"",'https://qakitchens.sharepoint.com/Shared Documents/QA Kitchens/Documents/Sophie Study/[QAkitchensSalesJanuary2022.xlsm]MonthlySalesFigures'!$F$37)
G7	G7	=IF(G7='https://qakitchens.sharepoint.com/Shared Documents/QA Kitchens/Documents/Sophie Study/[QAkitchensSalesJanuary2022.xlsm]MonthlySalesFigures'!$C$37=#REF!,"",'https://qakitchens.sharepoint.com/Shared Documents/QA Kitchens/Documents/Sophie Study/[QAkitchensSalesJanuary2022.xlsm]MonthlySalesFigures'!$G$37)
D8	D8	=IF(D8='C:\Users\Reception\QA Kitchens Pty Ltd\QA Kitchens Pty Ltd Team Site - QA Kitchens\Documents\Sophie Study\[QAkitchensSalesFebruary2022.xlsm]MonthlySalesFigures'!$D$37=#REF!,"",'C:\Users\Reception\QA Kitchens Pty Ltd\QA Kitchens Pty Ltd Team Site - QA Kitchens\Documents\Sophie Study\[QAkitchensSalesFebruary2022.xlsm]MonthlySalesFigures'!$D$37)
E8	E8	=IF(E8='C:\Users\Reception\QA Kitchens Pty Ltd\QA Kitchens Pty Ltd Team Site - QA Kitchens\Documents\Sophie Study\[QAkitchensSalesFebruary2022.xlsm]MonthlySalesFigures'!$C$37=#REF!,"",'C:\Users\Reception\QA Kitchens Pty Ltd\QA Kitchens Pty Ltd Team Site - QA Kitchens\Documents\Sophie Study\[QAkitchensSalesFebruary2022.xlsm]MonthlySalesFigures'!$E$37)
F8	F8	=IF(F8='C:\Users\Reception\QA Kitchens Pty Ltd\QA Kitchens Pty Ltd Team Site - QA Kitchens\Documents\Sophie Study\[QAkitchensSalesFebruary2022.xlsm]MonthlySalesFigures'!$C$37=#REF!,"",'C:\Users\Reception\QA Kitchens Pty Ltd\QA Kitchens Pty Ltd Team Site - QA Kitchens\Documents\Sophie Study\[QAkitchensSalesFebruary2022.xlsm]MonthlySalesFigures'!$F$37)
G8	G8	=IF(G8='C:\Users\Reception\QA Kitchens Pty Ltd\QA Kitchens Pty Ltd Team Site - QA Kitchens\Documents\Sophie Study\[QAkitchensSalesFebruary2022.xlsm]MonthlySalesFigures'!$C$37=#REF!,"",'C:\Users\Reception\QA Kitchens Pty Ltd\QA Kitchens Pty Ltd Team Site - QA Kitchens\Documents\Sophie Study\[QAkitchensSalesFebruary2022.xlsm]MonthlySalesFigures'!$G$37)
D9	D9	=IF(D9='C:\Users\Reception\QA Kitchens Pty Ltd\QA Kitchens Pty Ltd Team Site - QA Kitchens\Documents\Sophie Study\[QAkitchensSalesMarch2022.xlsm]MonthlySalesFigures'!$D$37=#REF!,"",'C:\Users\Reception\QA Kitchens Pty Ltd\QA Kitchens Pty Ltd Team Site - QA Kitchens\Documents\Sophie Study\[QAkitchensSalesMarch2022.xlsm]MonthlySalesFigures'!$D$37)
E9	E9	=IF(E9='C:\Users\Reception\QA Kitchens Pty Ltd\QA Kitchens Pty Ltd Team Site - QA Kitchens\Documents\Sophie Study\[QAkitchensSalesMarch2022.xlsm]MonthlySalesFigures'!$C$37=#REF!,"",'C:\Users\Reception\QA Kitchens Pty Ltd\QA Kitchens Pty Ltd Team Site - QA Kitchens\Documents\Sophie Study\[QAkitchensSalesMarch2022.xlsm]MonthlySalesFigures'!$E$37)
F9	F9	=IF(F9='C:\Users\Reception\QA Kitchens Pty Ltd\QA Kitchens Pty Ltd Team Site - QA Kitchens\Documents\Sophie Study\[QAkitchensSalesMarch2022.xlsm]MonthlySalesFigures'!$C$37=#REF!,"",'C:\Users\Reception\QA Kitchens Pty Ltd\QA Kitchens Pty Ltd Team Site - QA Kitchens\Documents\Sophie Study\[QAkitchensSalesMarch2022.xlsm]MonthlySalesFigures'!$F$37)
G9	G9	=IF(G9='C:\Users\Reception\QA Kitchens Pty Ltd\QA Kitchens Pty Ltd Team Site - QA Kitchens\Documents\Sophie Study\[QAkitchensSalesMarch2022.xlsm]MonthlySalesFigures'!$C$37=#REF!,"",'C:\Users\Reception\QA Kitchens Pty Ltd\QA Kitchens Pty Ltd Team Site - QA Kitchens\Documents\Sophie Study\[QAkitchensSalesMarch2022.xlsm]MonthlySalesFigures'!$G$37)
D10	D10	=IF(D10='C:\Users\Reception\QA Kitchens Pty Ltd\QA Kitchens Pty Ltd Team Site - QA Kitchens\Documents\Sophie Study\[QAkitchensSalesApril2022.xlsm]MonthlySalesFigures'!$D$37=#REF!,"",'C:\Users\Reception\QA Kitchens Pty Ltd\QA Kitchens Pty Ltd Team Site - QA Kitchens\Documents\Sophie Study\[QAkitchensSalesApril2022.xlsm]MonthlySalesFigures'!$D$37)
E10	E10	=IF(E10='C:\Users\Reception\QA Kitchens Pty Ltd\QA Kitchens Pty Ltd Team Site - QA Kitchens\Documents\Sophie Study\[QAkitchensSalesApril2022.xlsm]MonthlySalesFigures'!$C$37=#REF!,"",'C:\Users\Reception\QA Kitchens Pty Ltd\QA Kitchens Pty Ltd Team Site - QA Kitchens\Documents\Sophie Study\[QAkitchensSalesApril2022.xlsm]MonthlySalesFigures'!$E$37)
F10	F10	=IF(F10='C:\Users\Reception\QA Kitchens Pty Ltd\QA Kitchens Pty Ltd Team Site - QA Kitchens\Documents\Sophie Study\[QAkitchensSalesApril2022.xlsm]MonthlySalesFigures'!$C$37=#REF!,"",'C:\Users\Reception\QA Kitchens Pty Ltd\QA Kitchens Pty Ltd Team Site - QA Kitchens\Documents\Sophie Study\[QAkitchensSalesApril2022.xlsm]MonthlySalesFigures'!$F$37)
G10	G10	=IF(G10='C:\Users\Reception\QA Kitchens Pty Ltd\QA Kitchens Pty Ltd Team Site - QA Kitchens\Documents\Sophie Study\[QAkitchensSalesApril2022.xlsm]MonthlySalesFigures'!$C$37=#REF!,"",'C:\Users\Reception\QA Kitchens Pty Ltd\QA Kitchens Pty Ltd Team Site - QA Kitchens\Documents\Sophie Study\[QAkitchensSalesApril2022.xlsm]MonthlySalesFigures'!$G$37)
C7	C7	=IF(C7=[QAkitchensSalesJanuary2023.xlsm]MonthlySalesFigures!$C$37=#REF!,"",[QAkitchensSalesJanuary2023.xlsm]MonthlySalesFigures!$C$37)
C8	C8	=IF(C8='C:\Users\Reception\QA Kitchens Pty Ltd\QA Kitchens Pty Ltd Team Site - QA Kitchens\Documents\Sophie Study\[QAkitchensSalesFebruary2022.xlsm]MonthlySalesFigures'!$C$37=#REF!,"",'C:\Users\Reception\QA Kitchens Pty Ltd\QA Kitchens Pty Ltd Team Site - QA Kitchens\Documents\Sophie Study\[QAkitchensSalesFebruary2022.xlsm]MonthlySalesFigures'!$C$37)
C9	C9	=IF(C9='C:\Users\Reception\QA Kitchens Pty Ltd\QA Kitchens Pty Ltd Team Site - QA Kitchens\Documents\Sophie Study\[QAkitchensSalesMarch2022.xlsm]MonthlySalesFigures'!$C$37=#REF!,"",'C:\Users\Reception\QA Kitchens Pty Ltd\QA Kitchens Pty Ltd Team Site - QA Kitchens\Documents\Sophie Study\[QAkitchensSalesMarch2022.xlsm]MonthlySalesFigures'!$C$37)
C10	C10	=IF(C10='C:\Users\Reception\QA Kitchens Pty Ltd\QA Kitchens Pty Ltd Team Site - QA Kitchens\Documents\Sophie Study\[QAkitchensSalesApril2022.xlsm]MonthlySalesFigures'!$C$37=#REF!,"",'C:\Users\Reception\QA Kitchens Pty Ltd\QA Kitchens Pty Ltd Team Site - QA Kitchens\Documents\Sophie Study\[QAkitchensSalesApril2022.xlsm]MonthlySalesFigures'!$C$37)
C11	C11	=IF(C11='C:\Users\Reception\QA Kitchens Pty Ltd\QA Kitchens Pty Ltd Team Site - QA Kitchens\Documents\Sophie Study\[QAkitchensSalesMay2022.xlsm]MonthlySalesFigures'!$C$37=#REF!,"",'C:\Users\Reception\QA Kitchens Pty Ltd\QA Kitchens Pty Ltd Team Site - QA Kitchens\Documents\Sophie Study\[QAkitchensSalesMay2022.xlsm]MonthlySalesFigures'!$C$37)
C12	C12	=IF(C12='C:\Users\Reception\QA Kitchens Pty Ltd\QA Kitchens Pty Ltd Team Site - QA Kitchens\Documents\Sophie Study\[QAkitchensSalesJune2022.xlsm]MonthlySalesFigures'!$C$37=#REF!,"",'C:\Users\Reception\QA Kitchens Pty Ltd\QA Kitchens Pty Ltd Team Site - QA Kitchens\Documents\Sophie Study\[QAkitchensSalesJune2022.xlsm]MonthlySalesFigures'!$C$37)
C13	C13	=IF(C13='C:\Users\Reception\QA Kitchens Pty Ltd\QA Kitchens Pty Ltd Team Site - QA Kitchens\Documents\Sophie Study\[QAkitchensSalesJuly2022.xlsm]MonthlySalesFigures'!$C$37=#REF!,"",'C:\Users\Reception\QA Kitchens Pty Ltd\QA Kitchens Pty Ltd Team Site - QA Kitchens\Documents\Sophie Study\[QAkitchensSalesJuly2022.xlsm]MonthlySalesFigures'!$C$37)
C14	C14	=IF(C14='C:\Users\Reception\QA Kitchens Pty Ltd\QA Kitchens Pty Ltd Team Site - QA Kitchens\Documents\Sophie Study\[QAkitchensSalesAugust2022.xlsm]MonthlySalesFigures'!$C$37=#REF!,"",'C:\Users\Reception\QA Kitchens Pty Ltd\QA Kitchens Pty Ltd Team Site - QA Kitchens\Documents\Sophie Study\[QAkitchensSalesAugust2022.xlsm]MonthlySalesFigures'!$C$37)
C15	C15	=IF(C15='C:\Users\Reception\QA Kitchens Pty Ltd\QA Kitchens Pty Ltd Team Site - QA Kitchens\Documents\Sophie Study\[QAkitchensSalesSeptember2022.xlsm]MonthlySalesFigures'!$C$37=#REF!,"",'C:\Users\Reception\QA Kitchens Pty Ltd\QA Kitchens Pty Ltd Team Site - QA Kitchens\Documents\Sophie Study\[QAkitchensSalesSeptember2022.xlsm]MonthlySalesFigures'!$C$37)
C16	C16	=IF(C16='C:\Users\Reception\QA Kitchens Pty Ltd\QA Kitchens Pty Ltd Team Site - QA Kitchens\Documents\Sophie Study\[QAkitchensSalesOctober2022.xlsm]MonthlySalesFigures'!$C$37=#REF!,"",'C:\Users\Reception\QA Kitchens Pty Ltd\QA Kitchens Pty Ltd Team Site - QA Kitchens\Documents\Sophie Study\[QAkitchensSalesOctober2022.xlsm]MonthlySalesFigures'!$C$37)
C17	C17	=IF('https://qakitchens.sharepoint.com/Shared Documents/QA Kitchens/Documents/Sophie Study/[QAkitchensSalesNovember2022.xlsm]MonthlySalesFigures'!$C$37=#REF!,"",'https://qakitchens.sharepoint.com/Shared Documents/QA Kitchens/Documents/Sophie Study/[QAkitchensSalesNovember2022.xlsm]MonthlySalesFigures'!$C$37)
C18	C18	='https://qakitchens.sharepoint.com/Shared Documents/QA Kitchens/Documents/Sophie Study/[QAkitchensSalesDecember2022.xlsm]MonthlySalesFigures'!$C$37
D18	D18	='https://qakitchens.sharepoint.com/Shared Documents/QA Kitchens/Documents/Sophie Study/[QAkitchensSalesDecember2022.xlsm]MonthlySalesFigures'!$D$37
C19	C19	=SUMIF(C7:C18,"<>#REF!",C7:C18)
D19:G19	D19	=SUM(D7:D18)
E18	E18	='https://qakitchens.sharepoint.com/Shared Documents/QA Kitchens/Documents/Sophie Study/[QAkitchensSalesDecember2022.xlsm]MonthlySalesFigures'!$E$37
F18	F18	='https://qakitchens.sharepoint.com/Shared Documents/QA Kitchens/Documents/Sophie Study/[QAkitchensSalesDecember2022.xlsm]MonthlySalesFigures'!$F$37
G18	G18	='https://qakitchens.sharepoint.com/Shared Documents/QA Kitchens/Documents/Sophie Study/[QAkitchensSalesDecember2022.xlsm]MonthlySalesFigures'!$G$37
E20	E20	=SUM(C19:G19)

The December section was my link testing to an existing external workbook. I then tried to paste the link and change the file name BEFORE the November workbook file existed and came up with the #REF! error.
I then spent a lot of time trying to come up with methods to hide this error if the workbook doesn't yet exist which I thought I had figured out throughout the cells I then began filling in and adjusting the file names and cell references to suit.
I then remembered to try changing the original test format in November and again showed the #REF! error, and if I created a new file from template and named it and filled in some numbers for Person 1, January, it doesn't grab the data and I'm back to completely lost.
I thought I had it and now I don't and oh my goodness this project is the last thing to complete to finish my study and I just cannot do it.
Do you need me to include any minisheets of the other test referenced files or anything as well?
Am I maybe going towards a method that could work or just wasting my time entirely and really need to tie down my friend to help me understand your VBA Module methods?

Thank you I am so sorry I am so painful!

 

[Worf]

Hi

I will get back to you during the weekend, maybe posting a sample workbook.

 

[SophieYaldwyn]

Hi

I will get back to you during the weekend, maybe posting a sample workbook.

Thank you, I really appreciate all your time trying to help me!

 

[Worf]

Hi

One thing you need is to reference another workbook, so that was what I offered at post #4.

Find below links to two test files; the power query workbook is where the data is stored, while the welcome file is the master workbook. Here is what goes on each cell at the master:

C6 – path to PQ file

C7 – PQ file name

D6 – PQ sheet name

D7 – PQ cell

C8 – formula that retrieves value. If the file does not exist it returns a file not found message, however if the sheet name is invalid, it will return an error.

power query1.xlsm

Shared with Dropbox

www.dropbox.com

Welcome to Excel1.xlsm

Shared with Dropbox

www.dropbox.com

 

[SophieYaldwyn]

Hi,
Thank you, I just wasn't understanding the 'how' or terminology I don't think correctly back at post #4.
I have tried implementing this and getting it to work various ways, but even on the straight Welcome to Excel file there is a #NAME? error which I have been unable to manage to fix.
I downloaded both files and tried pasting the file path entirely and also excluding the file name typed in C7 out of the path in C6 in case that was the problem but just continue to get the error.

Am I missing a step maybe?
I'm so sorry to be asking more!
Here is what I currently have in the test:

Welcome to Excel1.xlsm
	C	D
6	C:\Users\Signature\Downloads\power query1.xlsm	sheet3
7	power query1.xlsm	J9
8	#NAME?
Sheet1

Cell Formulas
Range	Formula
C6	C6	="C:\Users\Signature\Downloads\power query1.xlsm"
C7	C7	="power query1.xlsm"
C8	C8	=getval(C6,C7,D6,D7)

I finally thought I understood with your explanation of what each cell contained but can't get it to work.
Do I need to learn how to add in the VBA module you mentioned as the other method of referencing in #4?
Thanks again,
Sophie