Questions

Using an Excel Ref as a filename link to other data to display

+
0 Votes
Locked

Using an Excel Ref as a filename link to other data to display

JimAvanti
I have a database set up in Excel were I have many excel workbook files (One for each serial numbered item)where I keep track of important information about that item. I also have an overview sheet that lists each serial number in column A followed by important information pulled from the individual workbooks. I have to update each cell by hand to point to every workbook, sheet, and reference cell. There must be a way to use the serial number in column A as a filename reference to the other workbooks so I can just copy each row and update only the serial number cell. Does anyone know how to do this?

Thanks,
Jim
  • +
    0 Votes
    gs-techrepublic

    G'day,

    You can point to a value in another workbook by using a formula like:

    ='[Summary.xls]Sheet1'!A1

    This is saying that you want cell A1 of sheet Sheet1 in workbook Summary.xls

    To make this dynamic, let's assume you have the name of the file (without the .xls) in cell K9: "Summary"

    You can use the indirect function to build up the reference. Basically, indirect takes in a text string, and converts it into a reference.

    =INDIRECT("'[Summary.xls]Sheet1'!A1") is the same as the previous formula. Now, you just need to make the workbook name variable:

    =INDIRECT("'["&K9&".xls]Sheet1'!A1")

    this will go to the workbook with the name of whatever is in K9 and look at cell A1 of Sheet1. The & sign in the formula means concatenate - it joins the string '[ with the value in K9 and the remaininder of the string .xls...

    Hope this helps.

    +
    0 Votes
    JimAvanti

    I did try Indirect in the past as you stated and couldn't get it to work (Just tried it again). Maybe my version of excel? Even before I get to the workbook variable in a cell it won't work.

    Works:
    ='[FC93409063470062.xls]Sensor Configuration'!$C$25

    Won't work:
    =INDIRECT("'[FC93409063470062.xls]Sensor Configuration'!$C$25")

    This isn't something I NEED to get working anymore (I made a VB script that fills in all the links for me automatically), but it would be nice to know how to get it working with a formula.

    +
    0 Votes
    benickerson

    As Jim described, I have a need to dynamical
    ly build a filename in Excel for data retieval. I have not yet found a solution.

    I also found that INDIRECT does not work for this purpose.

    I have not been able to get any function to with concatonation for a filename.

  • +
    0 Votes
    gs-techrepublic

    G'day,

    You can point to a value in another workbook by using a formula like:

    ='[Summary.xls]Sheet1'!A1

    This is saying that you want cell A1 of sheet Sheet1 in workbook Summary.xls

    To make this dynamic, let's assume you have the name of the file (without the .xls) in cell K9: "Summary"

    You can use the indirect function to build up the reference. Basically, indirect takes in a text string, and converts it into a reference.

    =INDIRECT("'[Summary.xls]Sheet1'!A1") is the same as the previous formula. Now, you just need to make the workbook name variable:

    =INDIRECT("'["&K9&".xls]Sheet1'!A1")

    this will go to the workbook with the name of whatever is in K9 and look at cell A1 of Sheet1. The & sign in the formula means concatenate - it joins the string '[ with the value in K9 and the remaininder of the string .xls...

    Hope this helps.

    +
    0 Votes
    JimAvanti

    I did try Indirect in the past as you stated and couldn't get it to work (Just tried it again). Maybe my version of excel? Even before I get to the workbook variable in a cell it won't work.

    Works:
    ='[FC93409063470062.xls]Sensor Configuration'!$C$25

    Won't work:
    =INDIRECT("'[FC93409063470062.xls]Sensor Configuration'!$C$25")

    This isn't something I NEED to get working anymore (I made a VB script that fills in all the links for me automatically), but it would be nice to know how to get it working with a formula.

    +
    0 Votes
    benickerson

    As Jim described, I have a need to dynamical
    ly build a filename in Excel for data retieval. I have not yet found a solution.

    I also found that INDIRECT does not work for this purpose.

    I have not been able to get any function to with concatonation for a filename.