Announcement

Collapse
No announcement yet.

Excel Data Reference...

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Excel Data Reference...

    Hey peeps.

    I have a problem I have been pulling my hair out about for a while.
    I am looking for a way to take a reference from another file that is constructed from inputted cell references.

    I found a way to do this with INDEX, but for some reason, INDEX only works when the other workbook is open. I tried some other functions, but they do not seem to have any way to get a constructed text address into the input field.

    What I want to be able to do is specify the directory, the file name and the tab with a fixed cell reference to be able to grab numbers from different sheets on different directories.

    ANY help would be GREATLY appreciated!

  • #2
    Your best bet would be to utilize VB. I do not believe Excel by itself has the ability to parse or otherwise read closed external references.
    For all the reasons to smile, 'for the hell of it' has always been my favorite.

    Comment


    • #3
      Yea, Excel can only read opened files.

      Comment


      • #4
        I was able to do a direct reference, but since the files were not named using typical characters, they needed the ' marks on either side of the name.

        'N:\Project Blah\Documents\Calculations\[file name.xls]XL Tab'!$A$4 would be, I believe, the format.

        The difficulty is, when using things like concatenate (&) it can create the full address field from independent cell references (Say, file name, directory, etc) but I thionk it has a problem because you cannot enter the ' character in the text field and have it work OR enter the ' character before a cell reference (it thinks "B20" is now a text field rather than a cell reference)

        I could do it the long way, perhaps, with VB or a macro script that would construct the formula from pieces on the page, but that would require individual activation every time I changed something (and it would also be a PITA for someone not too familiar with VB!!!! )

        Anyway, thanks for the help guys. If you hear of any way to do this, let me know!

        Comment


        • #5
          Is there a way to escape the ' character, such as \' perhaps?
          National Sarcasm Society
          "Like we need your support."

          Comment


          • #6
            I can try, but I tried doing things like putting quiotes around it or indexing it or doing a lookup (there were several formulas, VALUE was one I think, that would grab something from a cell and import it.)

            The problem is, when it sees the ' as a text character in a cell reference, it takes it as such. If you type it in like =' then it does not see it as text.

            I was able to trick it into doing a composition with INDEX, but then I deleted the formula when it would not work on a closed workbook (stupid. I should have kept it for reference).

            The whole thing is, i have data values that are stored on different spreadsheets. These sheets are all named differently depending on the site that was being investigated. They are also in different directories. I would like to make a sheet that I could drop almost anywhere, type in the location I wanted to search, and have it grab those values.

            The easy way would have been to have a generic name that was the same in all cases and just make sure your lookup sheet was there with it, but I am not the only one on this project and I would like to make it versitile and idiot resistant....

            Comment


            • #7
              You'll need to create a link to an external spreadsheet and then alter the object properties of the reference through VBA, as was suggested.

              It's not THAT hard. In the past I've done something similar, but the change that was being driven by the values in cells resulted in altering the SQL statement behind an external data source, which was then linked to a SQL Server database, and then triggering a refreshing of the data set.

              Having the reference be to an external spreadsheet might add some wrinkles to it - like perhaps having to close and re-open the data source (?) - but I'd speculate that it isn't all that much more tricky.

              Comment


              • #8
                Sab, the key here would eb a static source, so no opening.

                I COULD, say, have the destination on the root, and want to get the info from "Site 51". So instead of going to that directory, naming the file something generic, copying the destination to that same directory, etc etc, I would want to put in an address field, compose an external cell reference, and then automatically pluck that value from the other sheet.

                The VBA solution does seem like the only way so far, but being unfamiliar with anything but frankensteining existing code samples, I am at a loss for what to do.

                Thanks for the info though!

                Comment


                • #9
                  With the VBA approach you should be able to treat the other file as an external data source. The best way would be to just read the other file in as an ODBC source and work with it from there.

                  At the point you have it read it you should be able to treat it just like you did previously but without any restrictions.


                  You can also manually set the column data types so you won't have any problems between text and numeric fields.

                  Comment


                  • #10
                    Originally posted by Wes View Post
                    Yea, Excel can only read opened files.
                    Not true. Excel can read from other closed Excel files without opening them and without the use of VBA.

                    Comment


                    • #11
                      Ninja... Go to your Developer tab and record a macro of what you are trying to do. This will give you a good starting point for the VBA portion of it. Then for the parts that don't work for every workbook/sheet work through it or post here. There's not much I can't do with Excel.

                      P.S. - If you don't see a Developer tab, go to your Excel Options. If you are using 2007 (which I hope you are) it will be on the first window "Popular" and it will be the third tick mark down "Show Developer Tab in Ribbon". This opens up a whole nother side of Excel where the Record Macro and VBA code can be accessed easily.

                      However, I'm sure you have already solved this problem or gave up so I'm probably typing this for no reason.

                      Comment


                      • #12
                        Originally posted by Cerwin_Vega View Post
                        Not true. Excel can read from other closed Excel files without opening them and without the use of VBA.
                        Nto true. Excel can only read another Excel files via link formulas while without opening it. You cannot do anything else such as index. Link formulas is very ineffective. Link a few cells is fine. I just cannot see someone manually creating hard linking for the entire table between one file to another.

                        As you can see what Ninja tried to do in another post afterward, he doesn't want to create hard/static external reference links:

                        Originally posted by Ninjahedge View Post
                        I can try, but I tried doing things like putting quiotes around it or indexing it or doing a lookup (there were several formulas, VALUE was one I think, that would grab something from a cell and import it.)

                        The problem is, when it sees the ' as a text character in a cell reference, it takes it as such. If you type it in like =' then it does not see it as text.

                        I was able to trick it into doing a composition with INDEX, but then I deleted the formula when it would not work on a closed workbook (stupid. I should have kept it for reference).

                        The whole thing is, i have data values that are stored on different spreadsheets. These sheets are all named differently depending on the site that was being investigated. They are also in different directories. I would like to make a sheet that I could drop almost anywhere, type in the location I wanted to search, and have it grab those values.

                        The easy way would have been to have a generic name that was the same in all cases and just make sure your lookup sheet was there with it, but I am not the only one on this project and I would like to make it versitile and idiot resistant....

                        Comment


                        • #13
                          CV, yeah.

                          We are coming to the end of this project and most of us here also work on 2K3....so....

                          I know what you are saying with recording a macro and tweaking the script. I have done that before (usually using the macro to give me the syntax and then manually editing the variables until it worked). The problem is, there did not seem to be any way to easily do this....


                          What would have worked the best would have probably to have just kept ALL the spreadsheets in the same file as tabs rather than 3-4 different sheets, but hindsight is 20/20 until... well, I will stop there!

                          What I would like to know, for future reference, would be a way to make a sheet that could look up references that you plug in at the start in address fields (say, for instance, giving a site name and having the concatenate put together a full directory path to grab the needed information from the file). The other fields could be put in seperately and hidden if this were to be used for another project with a different directory tree or moved to a different computer, but the important thing would just be a file that could update automatically w/o having to open anything else....

                          I am basically trying to make Excel behave like an intelligent database.....

                          Comment


                          • #14
                            Originally posted by Wes View Post
                            Nto true. Excel can only read another Excel files via link formulas while without opening it. You cannot do anything else such as index. Link formulas is very ineffective. Link a few cells is fine. I just cannot see someone manually creating hard linking for the entire table between one file to another.

                            As you can see what Ninja tried to do in another post afterward, he doesn't want to create hard/static external reference links:

                            Thats why you create the formula and either drag it to cells you want to reference or paste it to cells you want to reference. I've done it. That's all I've done for the last year and a half is create Excel spreadsheets.

                            Depending on the position of the cells you are referencing you would of course need to not use $ or in some cases you may need to use it.

                            You can also do a find and replace (within formulas) as another way to relieve the dilemn. [Where's spell check on here??]

                            Comment


                            • #15
                              Originally posted by Ninjahedge View Post
                              What I would like to know, for future reference, would be a way to make a sheet that could look up references that you plug in at the start in address fields (say, for instance, giving a site name and having the concatenate put together a full directory path to grab the needed information from the file). The other fields could be put in seperately and hidden if this were to be used for another project with a different directory tree or moved to a different computer, but the important thing would just be a file that could update automatically w/o having to open anything else....
                              I almost know what you are trying to say but without knowing for sure I can't answer, but if you were to upload an example I would be more than happy to show you. I'll PM you my email and if you really want to know email me and I'll hook it up.


                              Originally posted by Ninjahedge View Post
                              I am basically trying to make Excel behave like an intelligent database.....
                              hehe... Funny you say that because I'm so used to using Excel and Access with VBA using Excel as my local database and being able to do anything I needed to do. Now in the corporate world it's all SQL Server/Oracle and .NET which SQL I was never fully savvy with and .NET only partially familiar... Thank God for intellisense!

                              Comment

                              Working...
                              X