Change the name of a target sheet (in main sheet) so I can run a calculation based on the contents o

[Peter_55422]

change the name of a target sheet (in main sheet) so I can run a calculation based on the contents of that target file.  My main sheet and all target sheets are in the same folder on my desktop.

Main sheet goes and averages 25 numbers based on a file called "SEARCH TERM OF DESSERT CUPS.csv "  Now must populate each cell by changing out ''dessert cups'' with the next term in cell A2, A3, A4 . . . . 

Statement reads like this and works fine now to make catantate or somthing also work?

=AVERAGE('https://d.docs.live.net/9390b80ae143c1b2/Desktop/Search Term of (name)/[Search Term of Dessert Cups.csv]Search Term of Dessert Cups'!$F$2:$F$26)

[#2]

Hello @Peter_55422,

If I am understanding correctly... you would like to change any instance of "Dessert Cups" with the value in A2, A3, A4, etc... if so then for A2, that could be:

=AVERAGE(INDIRECT("'https://d.docs.live.net/9390b80ae143c1b2/Desktop/Search Term of (name)/[Search Term of "&A2&".csv]Search Term of "&A2&"'!$F$2:$F$26"))

[#3]

That produces a #REF! error
If I exclude the INDIRECT function then it comes up with #VALUE! error

[#4]

@Peter_55422 

In summary...

#REF! means that a reference no longer exist.

#VALUE! can point to a number of errors but in this instance means that a formula that expects to return a number does not return a number.

Please manually replace "Dessert Cups" in both instances with the exact value located in cell A2 to verify that the file location does indeed exist.

[#5]

@PReagan 

Thanks for hanging in there with me today!

We are making "some" progress . . . the folder and the file do exist, however, the path does not work unless the other sheet is open. Both the master sheet and all the named sheets are in the same folder.

I thought it might be because the files are on my OneDrive because I saw the little green check box to show it on-line was gone that was my first clue.

I moved the whole folder off OneDrive but the path ( to my c:) still does not work unless the target file is also open. Both the master sheet and all the named sheets are in the same folder.

ALSO NOTE: If I have the master file open and I fill a cell with "=average(  . . . and then try to navigate to the named-file and click it open . . . it will not allow me to click it open.  If I set up the formula while the master and the named-file are both open it all works fine.  But when I come back to the sheet after closing ... no luck

I have attached the sandbox folder I am working with.  It has the master file and 5 named-files.  The folder is named as I have it named on my C:

Thanks so very much for hanging in there with me!

[#6]

@Peter_55422 

It is not possible to reference closed workbooks via the INDIRECT() function. So I must apologize for that. Maybe the following thread will be helpful instead:

https://stackoverflow.com/questions/28461672/referencing-value-in-a-closed-excel-workbook-using-indi...