Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 23

Thread: Excel Data Reference...

  1. #11
    ﭢ p+ha+ta+l ﭢ Cerwin_Vega's Avatar
    Join Date
    Mar 2009
    Location
    Georgia
    Posts
    184
    Rep Power
    11

    Default

    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.

  2. #12
    Administrator Wes's Avatar
    Join Date
    Mar 2009
    Posts
    606
    Blog Entries
    2
    Rep Power
    10

    Default

    Quote 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:

    Quote 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....

  3. #13

    Default

    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.....

  4. #14
    ﭢ p+ha+ta+l ﭢ Cerwin_Vega's Avatar
    Join Date
    Mar 2009
    Location
    Georgia
    Posts
    184
    Rep Power
    11

    Default

    Quote 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??]

  5. #15
    ﭢ p+ha+ta+l ﭢ Cerwin_Vega's Avatar
    Join Date
    Mar 2009
    Location
    Georgia
    Posts
    184
    Rep Power
    11

    Default

    Quote 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.


    Quote 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!

  6. #16

    Default

    Well, the example would be like this.

    I do some volume calculations on one sheet. It is formatted out to be a presentable sheet, so it can only hold a certain number of items....

    You know, when I am free, I will just mail you what I am looking at. What would have been good would have been some automatic lookups to save me the trouble of updating constantly when a problem is found.

    Also, taking the Excel sheet and merging it (mail merge style) into a word document wa sthe other thing we did. We were limited to the number of columns in the sheet though... A problem with a complex report......

  7. #17
    Administrator Wes's Avatar
    Join Date
    Mar 2009
    Posts
    606
    Blog Entries
    2
    Rep Power
    10

    Default

    Quote Originally Posted by Cerwin_Vega View Post
    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.
    I work for Ernst & Young since 1990. I was hired as a Lotus 123 programmer. And when Excel got more popular, we switched to dual platform on both Lotus and Excel. By 1995, we switched to Excel exclusively. I think i have a slightly more experience on Excel than you.

    Quote Originally Posted by Cerwin_Vega View Post
    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??]
    The way you do it is just to mass produce whole bunch of static hard links. I yell at our tax partners everytime when they do that. When you have to deal with large amount of data across hundreds of files, external static links like that is the worse thing you can do.
    Last edited by Wes; 01-11-2011 at 05:29 AM.

  8. #18
    Administrator Wes's Avatar
    Join Date
    Mar 2009
    Posts
    606
    Blog Entries
    2
    Rep Power
    10

    Default

    Quote Originally Posted by Cerwin_Vega View Post
    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!
    Actually, it is much easier than you think. Normally, you use "Import External Data" via MSQuery to connect to Access database without writing a single code.

    For any SQL server such as MS SQL or Oracle, simply install an ODBC driver in Windows, and you can connect and import any data from the SQL server just like Access or just about any external sources.

  9. #19

    Default

    MSQuery

    Sorry, me only know how to frankenstein things to work well and look nice IN Excel... so I do not know what you are referring to......

    I really need to know though. I could be the best artist in the world, but if I only use the thick crayons........

  10. #20
    Administrator Wes's Avatar
    Join Date
    Mar 2009
    Posts
    606
    Blog Entries
    2
    Rep Power
    10

    Default

    The Import External Data function inside Excel is just another form or static import. It has to be pre-defined what and where to get the data from. It is not in the dynamic nature that you were looking for.

    The way you described it, you need VBA to do it for you.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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