Tjyylli

Gravity magic - How does it work?

Hacking a Safe Lock after 3 tries

Is it true that good novels will automatically sell themselves on Amazon (and so on) and there is no need for one to waste time promoting?

Are there verbs that are neither telic, or atelic?

How can I track script which gives me "command not found" right after the login?

A Cautionary Suggestion

Brexit - No Deal Rejection

Are there other languages, besides English, where the indefinite (or definite) article varies based on sound?

How to simplify this time periods definition interface?

How to write cleanly even if my character uses expletive language?

Are ETF trackers fundamentally better than individual stocks?

What options are left, if Britain cannot decide?

Why do Australian milk farmers need to protest supermarkets' milk price?

A limit with limit zero everywhere must be zero somewhere

How to use of "the" before known matrices

What are substitutions for coconut in curry?

Is there a data structure that only stores hash codes and not the actual objects?

Unexpected result from ArcLength

A link redirect to http instead of https: how critical is it?

What should tie a collection of short-stories together?

Most cost effective thermostat setting: consistent temperature vs. lowest temperature possible

What is the significance behind "40 days" that often appears in the Bible?

Should we release the security issues we found in our product as CVE or we can just update those on weekly release notes?

What did Alexander Pope mean by "Expletives their feeble Aid do join"?

How do you make a dynamic link to another workbook?

Excel Macro: compare separate workbook lists and LINK to the data in a corresponding column?Dynamically reference a Named Table Column (via cell content) in ExcelHow to create a dynamic date title in excel 2010 using different tables and dataHow to use INDIRECT with a SUMIF formula for a date rangefind column/row value, sum values in another column on rows after, until next row valueHow do you insert weekends into data only containing weekday dates?Display Show List when Value selected in CellHow to switch which sheet another Excel sheet is looking at?How do I perform a calculation in rows that meet several conditions?Excel 2016 Pivot Table Column rename

0

I have numerous workbooks titled in the format "Year Detailed", e.g. "2000 Detailed.xlsx", "2001 Detailed.xlsx", ... "2018 Detailed.xlsx".

I am trying to make a summary sheet in another workbook, Summary.xlsx.

Is there a way to dynamically link to another workbook where part of the workbook name is based on the value of a cell?

For example, column A is a list of years starting A1 = 2001, and column B is a "total" column that looks at the workbook for "2001 Detailed" and does some math magic to return a value.

So this does what I want

=SUM('[2001 Detailed.xlsx]Friday Detail'!$K$4,'[2001 Detailed.xlsx]Saturday Detail'!$K$4)

But I then have to re-write it for each year to update the referenced file to the next year.

I have tried to make a Defined Name to do it. I have tried to concatenate in the formula but it just opens a Finder window to open the file.

microsoft-excel worksheet-function microsoft-excel-2016

share

asked 1 min ago

AndrewAndrew

1246

add a comment | 

0

I have numerous workbooks titled in the format "Year Detailed", e.g. "2000 Detailed.xlsx", "2001 Detailed.xlsx", ... "2018 Detailed.xlsx".

I am trying to make a summary sheet in another workbook, Summary.xlsx.

Is there a way to dynamically link to another workbook where part of the workbook name is based on the value of a cell?

For example, column A is a list of years starting A1 = 2001, and column B is a "total" column that looks at the workbook for "2001 Detailed" and does some math magic to return a value.

So this does what I want

=SUM('[2001 Detailed.xlsx]Friday Detail'!$K$4,'[2001 Detailed.xlsx]Saturday Detail'!$K$4)

But I then have to re-write it for each year to update the referenced file to the next year.

I have tried to make a Defined Name to do it. I have tried to concatenate in the formula but it just opens a Finder window to open the file.

microsoft-excel worksheet-function microsoft-excel-2016

share

asked 1 min ago

AndrewAndrew

1246

add a comment | 

I have numerous workbooks titled in the format "Year Detailed", e.g. "2000 Detailed.xlsx", "2001 Detailed.xlsx", ... "2018 Detailed.xlsx".

I am trying to make a summary sheet in another workbook, Summary.xlsx.

Is there a way to dynamically link to another workbook where part of the workbook name is based on the value of a cell?

For example, column A is a list of years starting A1 = 2001, and column B is a "total" column that looks at the workbook for "2001 Detailed" and does some math magic to return a value.

So this does what I want

=SUM('[2001 Detailed.xlsx]Friday Detail'!$K$4,'[2001 Detailed.xlsx]Saturday Detail'!$K$4)

But I then have to re-write it for each year to update the referenced file to the next year.

I have tried to make a Defined Name to do it. I have tried to concatenate in the formula but it just opens a Finder window to open the file.

microsoft-excel worksheet-function microsoft-excel-2016

share

asked 1 min ago

AndrewAndrew

1246

=SUM('[2001 Detailed.xlsx]Friday Detail'!$K$4,'[2001 Detailed.xlsx]Saturday Detail'!$K$4)

share

asked 1 min ago

AndrewAndrew

1246

share

asked 1 min ago

AndrewAndrew

1246

asked 1 min ago

AndrewAndrew

1246

asked 1 min ago

AndrewAndrew

1246