Linking Spreadsheets, and Referencings Cells to do so

[chrono2483]

Hello Everyone,

Each week, I create a spreadsheet, containing data and the file name is based on the week in question. With each spreadsheet, I have formulas linking back to the previous week to compare week over week.

The formula looks as so:

=SUM('T:\[STOCK, April 24 to April 30, 2016.xlsm]INVENTORY'!$C14:$I14

What I do each week, I manually have to update the date only in each row. I've tried find/replace, and despite highlighting the column of cells I want to focus on - it changes all occurrences of the date.

What I want to do is:
- Update the data in a single cell only (ie. A1 = May 1 to May 7)
- Instead of having the date built into the formula itself, have it reference A1 for the date portion only
- Result would be all rows will be automatically updated, based on the date in cell A1
- Ex: =SUM('T:\[STOCK, "$A$1", 2016.xlsm]INVENTORY'!$C14:$I14

Is this do-able, with linking spreadsheets?

Thank you!!!

 

[Reshaw]

I think you can use Indirect function to do this, you can build the string that references the sheet and cell range as a string and not as a cell reference.

if you insert a row the cell references will alter the range to suit, but the Indirect function will not.

I guess it would look like this:-

=SUM( INDIRECT("T:\[STOCK, " & $A$1 & ", 2016.xlsm]INVENTORY'!$C14:$I14") )

Or if the cell A1 contains the entire string for the filename, it would be simply:-
=SUM( INDIRECT($A$1) )

 

[chrono2483]

Thanks Reshaw -

Cell A1 = April 24 to April 30

I tried the above formula - but get a #REF! error.

Anything I could be doing wrong?

Thanks!

 

[Reshaw]

#REF errors means the file name and path specified does not exist. In my experience this is nearly always a typo within the indirect. If you press the f(x) button next to the formula bar then click in the INDIRECT word itself, it will show you the results it is generating. Check that all the spaces and things are in correctly and exactly the same as your file name.

From your description I don't think you are doing this but just in case:
If the A1 cell has a date in it in excel date format you will have to strip the values out of it. Just remember that Excel handles dates a bit weird.

Something along these lines:
=SUM( INDIRECT("T:\[STOCK, " & TEXT($A$1,"dd/mm/yyyy") & ", 2016.xlsm]INVENTORY'!$C14:$I14") )

Adjust the second argument in the TEXT function to whatever format you use to name your files.

 

[chrono2483]

#REF errors means the file name and path specified does not exist. In my experience this is nearly always a typo within the indirect. If you press the f(x) button next to the formula bar then click in the INDIRECT word itself, it will show you the results it is generating. Check that all the spaces and things are in correctly and exactly the same as your file name.

From your description I don't think you are doing this but just in case:
If the A1 cell has a date in it in excel date format you will have to strip the values out of it. Just remember that Excel handles dates a bit weird.

Something along these lines:
=SUM( INDIRECT("T:\[STOCK, " & TEXT($A$1,"dd/mm/yyyy") & ", 2016.xlsm]INVENTORY'!$C14:$I14") )

Adjust the second argument in the TEXT function to whatever format you use to name your files.

Unfortunately no luck. By typing the date range manually (ie. April 24 to April 30) - it returned the desired value, but using the suggested formulas still return a #REF:

=SUM( INDIRECT("T:\[STOCK, " & TEXT($A$1,"dd/mm/yyyy") & ", 2016.xlsm]INVENTORY'!$C14:$I14") ) : Returned #REF
=SUM( INDIRECT("T:\[STOCK, " & $A$1 & ", 2016.xlsm]INVENTORY'!$C14:$I14") ) : Where $A$1 = April 24 to April 30, Returned #REF


Knowing the value in A1 is in text format, I made sure to format A1 into text format, but still #REF.

Would anyone know where I am going wrong?

 

[Reshaw]

Apologies, after looking into this a bit more and trying to reproduce it myself, it transpires that you can only use indirect to another open work book. if the reference book is closed you get the "REF#" error.

I believe INDEX function may work for you in its place, it works slightly differently, I looked at the discussion here: an option instead of indirect function - trying to link to another - Microsoft Community


The basic format of the function is thus:
=INDEX('C:\Users\Shane\Documents\[AutoOpen.xlsm]Data'!$A$4:$E$20, X, Y )


Where X and Y are the row and column numbers you are referencing

I have not tried this yet, but it would appear the method I originally gave you won't work unless all the potential source files are open. I'm guessing this will not work for you as desired.

Regards
Robin