How to reference closed workbook

One way of being able to work with linked workbooks is firstly to open the workbook that is the source for the links then open the workbook with the links. Do whatever work you have to do and then go to the View tab and click on Save Workspace. A dialogue will open for you to give a location and name just like saving any other workbook. Give a name and save. Close the workbooks and save if prompted.

To open, open the Workspace file that you created and both workbooks will open without the trouble of opening both individually and without the hassle of the messages. When you have finished working on the workbooks, save them as usual. The next time that you open the workspace file, both workbooks will open again ready for work. The workbook from which the workspace was created may open with a small workspace window. Just maximize it and start working.

This may or may not work with sharepoint.

I didn't download your file.

I have no experience with sharepoint services.

Try using A1 style referencing in the formula. And don't use entire columns!

You are using some "table nomenclature" in your formula. The example workbook has a range of data but not a data table so you can't use table nomenclature to address the values in the range.

Columns are lettered starting with A and rows are numbered starting with 1 so, A1 refers to the top left cell on a worksheet.

This formula will process your formula but it is by no means automatically addressing a closed workbook. Maybe using VBA this will work with a closed workbook but you will be prompted for the location of the workbook otherwise....at least my experiments with the enclosed files prompted me every time.

Formula:

Please Login or Register  to view this content.

This version of the formula uses cells A1:C1 for Bob, 2016, Blue

Formula:

Please Login or Register  to view this content.

Originally Posted by excelnewbie716

Hi Tony,

What is A1 style referencing?

OK, this just an example. You'll have to use the correct ranges and sheet name.

Instead of this:

=SUMPRODUCT(--('C:\Users\username\desktop\workbookB.xlsx'!TabA[Manager]="Bob"),--(C:\Users\username\desktop\workbookB.xlsx'!TabA[Start Date]>=DATE(2016,1,1),--(C:\Users\username\desktop\workbookB.xlsx'!TabA[Group]="Blue"))

Do this:

=SUMPRODUCT(--('C:\Users\username\desktop\[workbookB.xlsx]SheetName'!A1:A10="Bob"),--(C:\Users\username\desktop\[workbookB.xlsx[SheetName'!B1:B10>=DATE(2016,1,1)),--(C:\Users\username\desktop\[workbookB.xlsx]SheetName'!B1:B10="Blue"))

To allow for future data addition just use a bigger range (but not too big!).