use text in a cell reference in a formula

bobcar_79

New Member
Joined
Apr 1, 2009
Messages
4
is there a way to link to several worksheets in one workbook based on a list in another?
say for example, i have a list of names in workbook1. in workbook2 each name on the list in workbook1 has it's own worksheet. in workbook1, i need to pull the value in cell A1 for each name on the list from it's respective tab in workbook2. Any suggestions other than manually entering the formula for each name?
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,629
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
the total of all A1 cells for the sheets in the named range SheetList

=SUMPRODUCT(N(INDIRECT("'"&SheetList&"'!A1")))
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,629
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
If you wish to access each individual number and the sheet name is in cell N2

=INDIRECT("'"&N2&"'!A1")
 

bobcar_79

New Member
Joined
Apr 1, 2009
Messages
4
Thanks Dave! The only problem I have now is that the cell A1 is in another workbook. How do I join the INDIRECT formula with the workbook2 reference? So, in workbook1 I need to reference

[workbook2] and INDIRECT("'"&N2&"'!A1")

the indirect formula representing the worksheet within workbook2
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,629
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

I am not clear on your question.

What do you want to access or summarize.
Where is the information and where is the criteria.
How do you reference the criteria.

What are you trying to achieve? A total or an individual amount?

Why reference Workbook2 if you can reference the relevant sheet from your workbook1?

-------------

Workbook2.xls 1a 100

=INDIRECT("'["&B5&"]"&C5&"'!$A$1")

N.B. Both files must be open

name of Workbook Workbook2.xls
Name of sheet 1a
 
Last edited:

bobcar_79

New Member
Joined
Apr 1, 2009
Messages
4
I have two workbooks which contain financial information. The first, [Variance Expl] contains all divisions of the company, but each division has it's own tab. The second workbook [Variance Matrix] has one tab with a list of all divisions. I need to go down the division list on [Variance Matrix] and link to A1 on each division's tab in [Variance Expl].

I need to keep the two workbooks separate as they are two different reporting mechanisms within the company and go to different groups of people.
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,629
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Very good.


With division name in C5

=INDIRECT("'[Variance Expl.xls]"&C5&"'!$A$1")

If you wish to try a formula that will work even if the other file is closed, download and install the Morefunc add-in.

The formula is then something like (edit the path as required)

=INDIRECT.EXT("'C:\Tech\[Variance Expl.xls]"&C5&"'!A1")

or

=INDIRECT.EXT("'[Variance Expl.xls]"&C5&"'!A1")
 

Watch MrExcel Video

Forum statistics

Threads
1,127,713
Messages
5,626,453
Members
416,186
Latest member
shamm28

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top