PDA

View Full Version : Excel Data Reference...



Ninjahedge
09-28-2010, 04:00 PM
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!

]LoL[Harm
09-28-2010, 09:18 PM
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.

Wes
09-29-2010, 04:55 AM
Yea, Excel can only read opened files.

Ninjahedge
09-29-2010, 08:02 AM
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!

Dr. Death
09-29-2010, 02:36 PM
Is there a way to escape the ' character, such as \' perhaps?

Ninjahedge
09-29-2010, 02:44 PM
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....

Saboteur
09-29-2010, 10:38 PM
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.

Ninjahedge
09-30-2010, 08:17 AM
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!

BENWA
10-03-2010, 11:58 AM
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.

Cerwin_Vega
01-09-2011, 01:45 PM
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.

Cerwin_Vega
01-09-2011, 03:05 PM
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. :D

Wes
01-09-2011, 05:44 PM
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:


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

Ninjahedge
01-10-2011, 08:09 AM
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.....

Cerwin_Vega
01-10-2011, 09:05 AM
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??] :D

Cerwin_Vega
01-10-2011, 09:14 AM
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. :)




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!

Ninjahedge
01-10-2011, 12:13 PM
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......

Wes
01-11-2011, 04:17 AM
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. ;)



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??] :D

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.

Wes
01-11-2011, 04:26 AM
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.

Ninjahedge
01-11-2011, 07:55 AM
:confused: MSQuery :confused:

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

Wes
01-11-2011, 11:40 AM
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.

Cerwin_Vega
01-11-2011, 12:10 PM
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.

I guess I should have elaborated a bit on this. When I was working in GA I was not able to get anything more than the MS suite. I couldn't get MS VS or SQL Server. Now I can get whatever I want/need.

Comcast has 2 main databases; both of which a typical user like I was only had read only access. That was why I used Access to pull in customer records then stored them in linked tables but Excel being what I've been used to via VBA it was much easier to export queried tables to Excel and manipulate them that way. Access 2007 is a POS working with queries. I like Access 2010 a lot better but now that I have MS SQL on my laptop and desktop in Philly Access is a thing of the past.


Pft... In regards to your experiance; allow me to quote my most favorite quote from Einstein... "Imagination is more important than knowledge, because knowledge is only what we know and imagination is everything." I don't doubt your experience at all Wes but I guess I just didn't understand the hedges need exactly.

P.S. - Living in Philly I will only be an hour and a half away from you. Don't make me come whip your butt in some UT99 LAN style!!! :D

Ninjahedge
01-11-2011, 12:44 PM
Meh, I did not mean to bring a contest of uber-geekdom into the fray guys. Sorry.

I am not (yet) in that league. But seeing how many years I have been complaining about things and doing nothing about it, I may be changing things up if I can find something I am willing to jump ship for....


I LIKE making spreadsheets that do weird thnigs. That also are user friendly, LOOk nice and save time.

Linking them with other proggies/data sources is paramount for me being able to leave Structural Engineering and try something else I might be more happy with, if just for the people I work with.......


Anyway, back to the original. I will see what I can do to post up some of the things we were trying to do (or mail one of you) and see, at the very least, what you think.

Thanks guys! You ARE the reasons I keep posting here!

Wes
01-11-2011, 01:52 PM
That was why I used Access to pull in customer records then stored them in linked tables but Excel being what I've been used to via VBA it was much easier to export queried tables to Excel and manipulate them that way. Access 2007 is a POS working with queries. I like Access 2010 a lot better but now that I have MS SQL on my laptop and desktop in Philly Access is a thing of the past.

You can use Excel to link diretly to SQL server as a read only linked tables as well, via ODBC driver. From Excel's standpoint, doesn't matter if it's Access 2007 or Access 2010 when you use the ODBC connection. You can use MSQuery's table relationship screen to create any type of inner/outer links by just drag and drop. Or simply just write the standard SQL statement inside MSQuery's SQL bo, and Excel will get the data linked in as a linked table. No VBA knowledge needed.

However, back to what Ninja needed, the linked source is predefined. That's not what he need.




Pft... In regards to your experiance; allow me to quote my most favorite quote from Einstein... "Imagination is more important than knowledge, because knowledge is only what we know and imagination is everything."

It is true up to only a limited sense. Since it's Excel we are talking about, the score is finite. Not unlimited like the universe.




I LIKE making spreadsheets that do weird thnigs. That also are user friendly, LOOk nice and save time.


That's great! That's the best attitude on making things more efficient and better. I held some advanced Excel seminar inside Ernst and Young and gave out some Excel challenge. I think you may benefit from learning a bit from it. I'll see if I can still find some of those old files...