# use text in a cell reference in a formula

#### bobcar_79

##### New Member
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?

### Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

#### Dave Patton

##### Well-known Member
the total of all A1 cells for the sheets in the named range SheetList

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

#### Dave Patton

##### Well-known Member
If you wish to access each individual number and the sheet name is in cell N2

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

#### bobcar_79

##### New Member
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

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
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.

#### bobcar_79

##### New Member
Dave. . .I've got it! Thanks for your help!

#### Dave Patton

##### Well-known Member
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")

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,623
Messages
5,838,422
Members
430,546
Latest member
CometOz

### 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?

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