# in the formula the workbook name is given from another cell

#### cvraman

##### New Member
Hi Guys,
Sorry if the question is stupid.
Is it possible to get the workbookname - in the below formula - from another cell?
e.a.: the workbook name is in cell C1 and the formula is in D1

='C:\[workbookname.xlsx]Sheet1'!\$A\$10

Is it possible to do that the workbook name is given into the formula from another cell without vba?

TamasDobos

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

#### Eric W

##### MrExcel MVP
Try using INDIRECT. For example:

=INDIRECT("'C:\["&C1&"]Sheet1'!\$A\$10")

#### cvraman

##### New Member
hi Eric,
The below one is working if both workbook is open but will result in #ref! if the referenced wb is closed.
Do you have any idea?

B1:= workbookname.xlsx
C1:= Sheet1
D1:= A1

=INDIRECT("'[" & B1 & "]" & \$C\$1 & "'!" & \$D\$1)
or
=INDIRECT("'C:\[" & B1 & "]" & \$C\$1 & "'!" & \$D\$1)

The original one:
='C:\[workbookname.xlsx]Sheet1'!\$A\$1

#### Eric W

##### MrExcel MVP
You are correct, you can't use INDIRECT to read a closed workbook. You can use something like:

=INDEX('C:\[workbookname.xlsx]Sheet1'!\$A\$1\$Z\$100,A1,B1)

and you can reference anything from
'C:\[workbookname.xlsx]Sheet1'!\$A\$1\$Z\$100 by changing A1 and B1 whether it's open or closed.. But regrettably, you can't use INDIRECT to change the workbook or sheet names.

Last edited:

#### cvraman

##### New Member
Thank you very much Eric

Replies
1
Views
366
Replies
1
Views
741
Replies
6
Views
451
Replies
1
Views
289
Replies
0
Views
323

1,191,170
Messages
5,985,063
Members
439,938
Latest member
MAlhash

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

### Which adblocker are you using?

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

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