I wonder if anyone can help me. I understand how to reference another workbook. However I am wanting to link another wookbook where the workbook name is quoted in the current sheet. I.E =[WK01.xlsx]Sheet1!$A$1However i am wanting to automatically get [workbook name] from column A workbookEARNINGS MONTUESWEDTHURSFRIWK01£150.00 WK02 WK3 WK4 WK5 WK6 WK7 WK8 WK9 WK10 Thanks in advance

LEE_STEWART You might use the INDIRECT function for this, but that only works if the workbook referred to is open in Excel, so you'd have to keep WK01.xlsx to WK10.xlsx open. Let's say WK01 is in cell A3. The formula in B3 could look like this: =INDIRECT("'["&A3&".xlsx]Sheet1'!A1") This can be filled down.

LEE_STEWART I agree with the advice given by HansVogelaar Here's a reference that may give further guidance on the use of INDIRECT: https://exceljet.net/formula/indirect-named-range-different-sheet

HansVogelaar ok thankyou, is there a way to do something similar where the wookbooks didn't have to be open. As this would progress to 52 weeks over the course of the year and thats a lot off wookbooks to have open at any one time just to compare and add up over the course of the year?

LEE_STEWART You haven't said exactly how large those weekly workbooks are, how many distinct spreadsheets there are in each weekly workbook. So let me ask a question of two:would it be possible to combine them as separate sheets all in the same workbook?might it even be possible to combine all of the data into a single data table (using column headings to differentiate days or weeks)? It's been my observations that many people manually separate the raw data (the input end of things) into days or weeks for clarity at the front end. Excel is really good (it "excels") in taking a single database (a.k.a. Table) and summarizing it, breaking it apart as needed for detail output reports, tabulating things. The Pivot Table is but one example of that....your summary presentation could easily be produced if all that data were in a single table to begin with, letting Excel do the heavy lifting of producing the summary numbers by day and week.

LEE_STEWART You may check this Referencing value in a closed Excel workbook using INDIRECT? if something will help, I didn't test what is suggested in it.

HELP ON REFERENCING ANOTHER WORKBOOK | Microsoft Community Hub

11 Replies

mathetes
Gold Contributor
Nov 29, 2020
LEE_STEWART

I agree with the advice given by HansVogelaar

Here's a reference that may give further guidance on the use of INDIRECT: https://exceljet.net/formula/indirect-named-range-different-sheet
HansVogelaar
MVP
Nov 29, 2020
LEE_STEWART

You might use the INDIRECT function for this, but that only works if the workbook referred to is open in Excel, so you'd have to keep WK01.xlsx to WK10.xlsx open.

Let's say WK01 is in cell A3.

The formula in B3 could look like this:

=INDIRECT("'["&A3&".xlsx]Sheet1'!A1")

This can be filled down.
- LEE_STEWART
  Copper Contributor
  Nov 29, 2020
  HansVogelaar
  
  ok thankyou, is there a way to do something similar where the wookbooks didn't have to be open. As this would progress to 52 weeks over the course of the year and thats a lot off wookbooks to have open at any one time just to compare and add up over the course of the year?
  - SergeiBaklan
    Diamond Contributor
    Nov 29, 2020
    LEE_STEWART
    
    You may check this Referencing value in a closed Excel workbook using INDIRECT? if something will help, I didn't test what is suggested in it.

Forum Discussion

HELP ON REFERENCING ANOTHER WORKBOOK

11 Replies

Resources

workbook	EARNINGS
	MON	TUES	WED	THURS	FRI
WK01	£150.00
WK02
WK3
WK4
WK5
WK6
WK7
WK8
WK9
WK10