An Excel Question - Tilted Forum Project Discussion Community

Fallon · 07-10-2003, 06:43 AM

Alright, what I'm trying to do is pull in a couple cells from a different workbook in Excel for work. I've played around with Vlookup and such, and those aren't working.
Currently, we're running Excel 97.
To try to clarify, I have data.xls, and data2.xls. I'm trying to pull some data from data2.xls into data.xls.
Any thoughts or help would be greatly appriciated by me and my boss.
Thanks.

thecow · 07-10-2003, 07:11 AM

I've used vlookup and hlookup, but I think that may only work with sheets in the same file. I know that probably doesn't help much(many reasons why you may want to keep the data seperate), but keep in mind as a last ditch effort. I'll start searching to see if I can find anything to help.

thecow · 07-10-2003, 07:19 AM

It looks like you might be able to use microsoft query to do what you want. Here is a page talking about it ( link ).

If it's not sensitive, what type of main file are you making. If it didn't have to be in excel, you would have all sorts of options - like putting the data in a MySQL table and using any number of languages to right a page that uses that data. Just some thoughts. Hope the link helps, and I'll still keep an eye out for you.

Fallon · 07-10-2003, 07:55 AM

We're trying to get data on how many things our people sell.
It doesn't look like I can use Microsoft Query because I need to get it from our Intranet first.

Fallon · 07-10-2003, 08:07 AM

k, Lemme try and clarify some more.
We're pulling data from an Excel Workbook at www.work.intranet.com/data.xls
and we're trying to put it into data2.xls.
We can't really do anything with the thing that's on our Intranet because the company is stuck in their ways, and it's an easy way for everyone to access it, so they don't want to make anymore work.

thecow · 07-10-2003, 08:10 AM

Ugh, I have no idea how to do what your talking about in excel. Sorry man.

Depending on how important it is, you could go the route of MySQL like I said. It would be relatively easy to have one comp on the intranet running SQL, and have the workers fill out a page and store their info on the server instead of locally. The page could be built in ASP or PHP, and have a form to enter all of the data they are currently putting in excel. Then you could easily create another page that queries the database to your hearts content.

This maybe overkill, but it would certainly solve the problem, and keep everything in a central database that could easily be backed up, or migrated.

Good luck to you, and if you need any other help feel free to PM me.

P.S. sorry, you posted the part about 'not wanting to change anything' while I was typing. good luck.

mtsgsd · 07-11-2003, 04:44 AM

You have to bring it in as external data. The help function will tell you how to use the wizard to create a query to pull the data in.

What they don't tell you is that you have to define an ODBC connection "DSN" that it can use to connect to the other workbook. Do that, and then tell the wizard to use it when making it's connection.

One thing to remember is that this is a "snapshot" of the data. You need to refresh it whenever you open data2.xls in order to make sure it has the latest copy of the data in data1.xls (unless it doesn't change).

07-10-2003, 06:43 AM	#1 (permalink)
Fallon Junkie Location: RI	An Excel Question Alright, what I'm trying to do is pull in a couple cells from a different workbook in Excel for work. I've played around with Vlookup and such, and those aren't working. Currently, we're running Excel 97. To try to clarify, I have data.xls, and data2.xls. I'm trying to pull some data from data2.xls into data.xls. Any thoughts or help would be greatly appriciated by me and my boss. Thanks.

07-10-2003, 07:11 AM	#2 (permalink)
thecow Crazy Location: Fayetteville, AR	I've used vlookup and hlookup, but I think that may only work with sheets in the same file. I know that probably doesn't help much(many reasons why you may want to keep the data seperate), but keep in mind as a last ditch effort. I'll start searching to see if I can find anything to help.

07-10-2003, 07:19 AM	#3 (permalink)
thecow Crazy Location: Fayetteville, AR	It looks like you might be able to use microsoft query to do what you want. Here is a page talking about it ( link ). If it's not sensitive, what type of main file are you making. If it didn't have to be in excel, you would have all sorts of options - like putting the data in a MySQL table and using any number of languages to right a page that uses that data. Just some thoughts. Hope the link helps, and I'll still keep an eye out for you.

07-10-2003, 07:55 AM	#4 (permalink)
Fallon Junkie Location: RI	We're trying to get data on how many things our people sell. It doesn't look like I can use Microsoft Query because I need to get it from our Intranet first.

07-10-2003, 08:07 AM	#5 (permalink)
Fallon Junkie Location: RI	k, Lemme try and clarify some more. We're pulling data from an Excel Workbook at www.work.intranet.com/data.xls and we're trying to put it into data2.xls. We can't really do anything with the thing that's on our Intranet because the company is stuck in their ways, and it's an easy way for everyone to access it, so they don't want to make anymore work.

07-10-2003, 08:10 AM	#6 (permalink)
thecow Crazy Location: Fayetteville, AR	Ugh, I have no idea how to do what your talking about in excel. Sorry man. Depending on how important it is, you could go the route of MySQL like I said. It would be relatively easy to have one comp on the intranet running SQL, and have the workers fill out a page and store their info on the server instead of locally. The page could be built in ASP or PHP, and have a form to enter all of the data they are currently putting in excel. Then you could easily create another page that queries the database to your hearts content. This maybe overkill, but it would certainly solve the problem, and keep everything in a central database that could easily be backed up, or migrated. Good luck to you, and if you need any other help feel free to PM me. P.S. sorry, you posted the part about 'not wanting to change anything' while I was typing. good luck.

07-11-2003, 04:44 AM	#7 (permalink)
mtsgsd Addict Location: Wisconsin, USA	You have to bring it in as external data. The help function will tell you how to use the wizard to create a query to pull the data in. What they don't tell you is that you have to define an ODBC connection "DSN" that it can use to connect to the other workbook. Do that, and then tell the wizard to use it when making it's connection. One thing to remember is that this is a "snapshot" of the data. You need to refresh it whenever you open data2.xls in order to make sure it has the latest copy of the data in data1.xls (unless it doesn't change).