Results 1 to 2 of 2

Thread: Returning the value of a cell in a different workbook - name of book in cell

  1. #1

    Returning the value of a cell in a different workbook - name of book in cell



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Apologies if the title of my thread is a little confusing.
    I am a teacher and have set up a large assessment system which involves each student having their own workbook.All of these are stored in the same folder.
    I have then created a tracker sheet which links to each individual students workbook.I need this tracker sheet to return the value of a cell in the individual student sheet.

    Each student book is named after their unique student ID number which is listed next to their name on the tracker sheet.
    I can manually return the value I need using this formula
    =('T:\ICT\Lesson Plans\Key Stage 3\2015-2016\Assessment\Students\[270737.xlsx]270737'!C2)
    As you can see the 270737 is the unique student ID.I need to replace this with a cell reference so I can copy the link down for the 698 students on the list.

    Any ideas?
    Thanks,
    Adrian

  2. #2
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    869
    Articles
    0
    Excel Version
    Excel 2013

    INDIRECT and INDIRECT.EXT functions (get data from opened/closed Workbook)

    Quote Originally Posted by adeking View Post
    As you can see the 270737 is the unique student ID.I need to replace this with a cell reference so I can copy the link down for the 698 students on the list.Any ideas?
    Try to use the INDIRECT function. (Note! workbooks must be open)
    In the 'A' column set names Workbook/Sheetname (enter the first two numbers, select both numbers and via Fill Handle copy down to last number.)
    Code:
    =INDIRECT("'T:\ICT\Lesson Plans\Key Stage 3\2015-2016\Assessment\Students\["&A2&".xlsx]"&A2&"'!C2")
    If you want to have a closed workbooks then use INDIRECT.EXT function (Morefunc free add-in for Excel)
    Code:
    =INDIRECT.EXT("'T:\ICT\Lesson Plans\Key Stage 3\2015-2016\Assessment\Students\["&A2&".xlsx]"&A2&"'!C2")
    see attach
    Attached Files Attached Files
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

Posting Permissions

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