Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Excel and linked filenames

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    58
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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;

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,658
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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?


  3. #3
    Board Regular
    Join Date
    Mar 2002
    Posts
    58
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    D1 has the Month, and D2 has the year.

    hviking

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,658
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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 ]

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Posts
    58
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks.

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

    Thanks;

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,658
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Posts
    58
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,658
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default


    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.

  9. #9
    Board Regular
    Join Date
    Mar 2002
    Posts
    58
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    OK. Well, at least it works.

    Thanks again;

    hviking

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •