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?
 

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"
the total of all A1 cells for the sheets in the named range SheetList

=SUMPRODUCT(N(INDIRECT("'"&SheetList&"'!A1")))
 
Upvote 0
If you wish to access each individual number and the sheet name is in cell N2

=INDIRECT("'"&N2&"'!A1")
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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")
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

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
Back
Top