Excel and linked filenames

hviking

Board Regular
Joined
Mar 31, 2002
Messages
61
Got a little problem here that some might be able to solve for me.

I'm creating a spreadsheet that takes data from another file and includes it on this spreadsheet. So far no problems. Now I want to be able to use cells D1 and D2 to name the workbook in the other file where the data comes from.

The current formula is

='[Budget2002.xls]February 2002'!B14

I want to be able to use D1 and D2 like this:

='[Budget2002.xls]D1&" "&D2'!B14

Is there a way to do this without using VB or macros? (Haven't really gotten the hang of them yet)

Also, how do I stop the users from going to a protected cell? Right now if I protect it, the user can still click on it but not change it. (a dialog box comes up) Is there a way of protecting the cell where the user can not select it at all in Excel 2000?

Thanks;
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
On 2002-04-01 10:04, hviking wrote:
Got a little problem here that some might be able to solve for me.

I'm creating a spreadsheet that takes data from another file and includes it on this spreadsheet. So far no problems. Now I want to be able to use cells D1 and D2 to name the workbook in the other file where the data comes from.

The current formula is

='[Budget2002.xls]February 2002'!B14

I want to be able to use D1 and D2 like this:

='[Budget2002.xls]D1&" "&D2'!B14

Is there a way to do this without using VB or macros? (Haven't really gotten the hang of them yet)

Also, how do I stop the users from going to a protected cell? Right now if I protect it, the user can still click on it but not change it. (a dialog box comes up) Is there a way of protecting the cell where the user can not select it at all in Excel 2000?

Thanks;

What are you putting in D1 and D2?
 
Upvote 0
On 2002-04-01 10:08, hviking wrote:
D1 has the Month, and D2 has the year.

hviking

=INDIRECT("'[Budget2002.xls]"&D1&" "&D2&"'!"&B14)
This message was edited by Aladin Akyurek on 2002-04-01 10:20
 
Upvote 0
Thanks.

I tried the formula, and I keep getting #ref. Am I doing something wrong?

Thanks;
 
Upvote 0
On 2002-04-01 10:39, hviking wrote:
Thanks.

I tried the formula, and I keep getting #ref. Am I doing something wrong?

Thanks;

=INDIRECT("'[Budget2002.xls]"&D1&" "&D2&"'!B14")

Hope it's right this time.

Aladin
 
Upvote 0
It works. Except for one tiny problem. If Budget2002 is not open, it still gives the #ref. If it is open it works fine. Is there a way of doing this without having the Budget2002 open?

Thanks for the great help so far.
 
Upvote 0

On 2002-04-01 11:20, hviking wrote:
It works. Except for one tiny problem. If Budget2002 is not open, it still gives the #ref. If it is open it works fine. Is there a way of doing this without having the Budget2002 open?


I'm afraid not.
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,995
Members
448,539
Latest member
alex78

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