Take Data From Other Files

How do I take data from other files?
I believe that somehow I would use the VLOOKUP function.
Say I have a source file I'm grabing from.  I want an entire column from my 
source file to be sucked in a particular file (working file), minus the 
first row (I have two files with different column names).  The source file 
changes occasionally (numbers of rows, data, etc.).  I want the information 
to be updated every time I open my working file.  How would I do this?

How would I do this if I have multiple source files?
Basically, this is the situation.

I have many suppliers.  Each has their own data file (names of products, 
descriptions, prices, etc.).  Then I have a working file (the one that I 
upload to my store front, with specific column names specified by the store 
front supplier).  What I want to be able to do is link to each of the 
supplier files inside of the working file, so that when I open my working 
file the data is dynamically updated.  Make sense?

The thing is is that the number of rows in each of the supplier files will 
constantly change (depending on what products are in stock).

-- 


0
John
1/15/2010 11:23:11 PM
excel 39879 articles. 2 followers. Follow

2 Replies
960 Views

Similar Articles

[PageSpeed] 22

The way I'd do it, but I'm in no way as elegant as others
in here...

Start by opening both your file and one of the vendor files.
Start in the vendor file and copy the first cell at the top
of the row. Switch to your spreadsheet and paste using "Paste
Special Link". Edit the cell and remove the dollar sign that
precedes the row number. Copy that and paste down as many rows
as the maximum amount of rows you'll ever need. You'll end up
with zeros at the bottom where there is no data in the vendor
file but you can hide those in Tools Option View.

Jordon

John Persico wrote:
> How do I take data from other files?
> I believe that somehow I would use the VLOOKUP function.
> Say I have a source file I'm grabing from. I want an entire column from
> my source file to be sucked in a particular file (working file), minus
> the first row (I have two files with different column names). The source
> file changes occasionally (numbers of rows, data, etc.). I want the
> information to be updated every time I open my working file. How would I
> do this?
>
> How would I do this if I have multiple source files?
> Basically, this is the situation.
>
> I have many suppliers. Each has their own data file (names of products,
> descriptions, prices, etc.). Then I have a working file (the one that I
> upload to my store front, with specific column names specified by the
> store front supplier). What I want to be able to do is link to each of
> the supplier files inside of the working file, so that when I open my
> working file the data is dynamically updated. Make sense?
>
> The thing is is that the number of rows in each of the supplier files
> will constantly change (depending on what products are in stock).
>

0
Jordon
1/15/2010 11:46:30 PM
This is an example for a case where both files are in the same workbook ---  
='3708EVAN'!E72  This places the value that is in E72 of File 3708EVAN in 
where that equation appears.    I have 6 rental units and each unit is a 
separate files in the workbook.  At the end of each files is a data base 
that keeps track of different items, ie. rent, car expense, labor, material, 
taxes etc.  Then I have one files that consolidates all of that and the 
above formula is what I use.
charles arnett
cjarnett1@who.rr.com

"Jordon" <jordon@REMOVETHISsamiamnot.com> wrote in message 
news:e#vehyjlKHA.5304@TK2MSFTNGP06.phx.gbl...
> The way I'd do it, but I'm in no way as elegant as others
> in here...
>
> Start by opening both your file and one of the vendor files.
> Start in the vendor file and copy the first cell at the top
> of the row. Switch to your spreadsheet and paste using "Paste
> Special Link". Edit the cell and remove the dollar sign that
> precedes the row number. Copy that and paste down as many rows
> as the maximum amount of rows you'll ever need. You'll end up
> with zeros at the bottom where there is no data in the vendor
> file but you can hide those in Tools Option View.
>
> Jordon
>
> John Persico wrote:
>> How do I take data from other files?
>> I believe that somehow I would use the VLOOKUP function.
>> Say I have a source file I'm grabing from. I want an entire column from
>> my source file to be sucked in a particular file (working file), minus
>> the first row (I have two files with different column names). The source
>> file changes occasionally (numbers of rows, data, etc.). I want the
>> information to be updated every time I open my working file. How would I
>> do this?
>>
>> How would I do this if I have multiple source files?
>> Basically, this is the situation.
>>
>> I have many suppliers. Each has their own data file (names of products,
>> descriptions, prices, etc.). Then I have a working file (the one that I
>> upload to my store front, with specific column names specified by the
>> store front supplier). What I want to be able to do is link to each of
>> the supplier files inside of the working file, so that when I open my
>> working file the data is dynamically updated. Make sense?
>>
>> The thing is is that the number of rows in each of the supplier files
>> will constantly change (depending on what products are in stock).
>>
> 
0
charles
1/16/2010 12:44:40 AM
Reply:

Similar Artilces:

How can I change from the exel file extention .xlsx to .xls ?
How can I change from the exel file extention .xlsx to .xls ? Since the other users can not open .xlsx extention. Hi Open the file in XL2007. Click circular Office button>Save As>Excel 97-2003 Workbook -- Regards Roger Govier "Suleyman" <Suleyman@discussions.microsoft.com> wrote in message news:2170066A-49AA-458B-A108-723195FE4DC4@microsoft.com... > How can I change from the exel file extention .xlsx to .xls ? > Since the other users can not open .xlsx extention. Roger Govier wrote: > Hi > > Open the file in XL2007. > Click circular Offi...

Bookmarks take me away from email
I use "Actions > Send Web Page by E-mail". Then setup the bookmark, and a link to the bookmark in Outlook. This link takes the recipient to the web page (www.webpage.com#bookmark), rather than jumping them to that part of the email. It seems like this should be possible since you can set the link to go to a bookmark. I don't even get how it thinks I would possibly want it to go to a web page. Help is much appreciated! ...

create sheets from data
I would like to see I can create a new sheet from souce data that we create a new sheet from a group of data and name the sheet after the group and copy the data into the sheet... so the below would create 3 new sheets called a,b,c and would hold the data against each group... so 1 sheet would be a and hold a1 to e5 data in it... a b c d e Group Data 1 a aa aa aa aa 2 a aa aa aa aa 3 a aa aa aa aa 4 a aa aa aa aa 5 a aa aa aa aa 6 b bbb bbb bbb bbb 7 b bbb bbb bbb bbb 8 b bbb bbb bbb bbb 9 b bbb bbb bbb bb...

you and another user are attempting to change the same data at the
Hello, I have an Access 2000 database on a shared drive. When attempting to open the database this morning, we get the error: The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time. However, no one is in the database, and there is no ldb file. I've tried copying the database to a local drive and got the same error. I also opened a blank database and attempted to import tables/queries/forms/macros and got the same error. Has anyone seen this before? Is there a way to get the database open or get ...

Take Ownership
What does it mean to Take Ownership of a file or folder? <PeoplesChoice@Chicago.net> wrote in message news:gjqqv5ddgj7uesie7vdej1tshbv49unr1u@4ax.com... > What does it mean to Take Ownership of a file or folder? It means to transfer ownership of an object from the original owner to you, (your account). Google ' Take Ownership ' for more information. To take ownership of a file or a folder How to take ownership of a file You must have ownership of a protected file in order to access it. If another user has restricted access and you are the computer administ...

Outlook 2003 cannot find my files
I am running Windows XP Professional Tablet. I upgraded to Office 2003, and now I cannot see my contacts and my calendar is empty. I do not know what to do now, as I cannot run the earlier version of Outlook. I can find the pst files, but I cannot figure out how to import them into this new version. In addition, I cannot see the local email files that I have stored in my directory. Help! Sincerely, GVG Don't try to import - use File->open->outlook data file and browse to the location of your .pst file. --� Milly Staples [MVP - Outlook] Post all replies to the group to ke...

changing data
I have a column of numbers. I want to make a column that adds a percentage to the first column of original data. Is this possible? column_1 column 2 100 125 10 12.50 Thanks, to anyone that replies Chris One way: B2: =A2*125% In article <D61B27B4-7E58-4325-B05E-00E4D95B8C61@microsoft.com>, "Kozzy" <Kozzy@discussions.microsoft.com> wrote: > I have a column of numbers. I want to make a column that adds a percentage > to the first column of original data. Is this possible? > > column_1 column 2 > 100 125 &...

Copying from data from another worksheet to exisiting.
Hi- Could someone tell me the way to copy a closed worksheet to the existing one. I have a macro in a template, and it gets kicked off every morning. I want to get the data from a another worksheet when it gets kicked off and then do the manipulation with that data.Could someone help!I would really appreciate it. Thanks jnair ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Business Software Galore! Free Excel Forum http://www.ozgrid.com/forum *** If the range doesn't change, maybe you could just use links: Open both workbooks. in the "receiving&quo...

Linking data between sheets
I am linking sheets together and have hit a snag. I have referenced cell in another sheet, (='Cheese Vat Sheet'!D2) that is the result of formula on that sheet. The problem I am having is that when I use the "IF" function in another cell, (B3) based on the linked cell I kee getting a, "false" result. Here is the formula I entered. =IF(B3=8,"(#3)",IF(B3=7,"(#3&4)",IF(B3=9,"0 "))) I've looked through my excel bible 2000 but havent really seen i referenced there. idea's? thanks Ji -- Message posted from http://www.E...

Office 2007
I have been working with Office 2007 beta. In prior versions I was able to use the mouse to highlight the cells that were to be added to a chart. I cannot highlight the cells that I need, but have to manually type in the beginning and ending cell range. ...

transferring data from external software
Hello all as regards transferring data from external software ( Priorti ) to Excel (the data will be passed as text file, delimited by tab, notation of fields of text by commas) the Excel delimit the fields both by commas and the tab. How it's possible to change default pattern of Excel so the delimeter will be tab only (the data are passed directly to Excel and therefore I don't have possibility for manual import of the data TNX, eyal semo I have found the answer: Data>Text to Columns>Delimited by "comma" Excel remembers the last use of it (Y?) eyal "Eyal Se...

calculation taking too long
Currently I have a sheet where I enter my data and then another sheet within the same workbook that makes various calculations based on this data. But every time I enter data, it takes a long time for my formulas to calculate, upwards of a minute. I am pretty sure this is because my formulas contain entire columns as ranges (i.e. A:A rather than A1:A100). However I need to use A:A in my formulas as my data sheet is indefinitely long and I will continue to add to this over the years. I don't want to have to go back and change all my formulas to A1:A200 every time my data e...

files size expands for no apparent reason when saving.
Using Office XP on Windows XP Professional. I have several excel files used as simple databases that I add to daily. The files size is at the end of the year is never over 1.5mb. I start a new file from a template yearly. Occassionaly, for no apparent reason the file size will jump to as much as 5mb and the only way to fix it is to copy and paste the data into a new file. This has happend in all excel files of this type from Office 97 to Office XP. Any suggestions? When you hit ctrl-End, do you go way past what you think should be the bottom right cell? If yes, then take a look at De...

pdf changes to dat files when sending
Have changed from Outlook Express to Outlook and having problems with MYOB (accounting program). We send our invoices through MYOB (it does all the work - configures everything and then send it as an attachment) and it sends them in a .pdf format through the default email. Some people are getting .pdf files and others are having .dat files coming to them. Not sure why the .dat files are going (all should be .pdf) and how do I fix it so all are .pdf files ... any help .. I am desperate! Don't use rich text format for your message settings- try plain text or HTML. Shelley wrote: > Ha...

pulling data 04-27-10
I'm not sure I am explaining this right I want the data from C21 on sheet 1 in a cell on sheet 2 if the A & B column data matches SHEET 1 row 21 A B C D E 4/21/2010 34287 74 3 4 SHEET 2 row 20 A B C D E 4/21/2010 34287 74 try =sumproduct((s1!a2:a22=a2)*(s1!b2:b22=b2)*s1!c2:c22) -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "Norm" <Norm@discussions.microsoft.com> wrote in message news:A40C8E08-721F-47BB-A73C-458AC...

pivot tables
i have a range of dates in one column and a number of data points in three other columns. i want a pivot table that will show a line graph of the data points (y axis) over time (the x axis). please help :) ...

Logging in taking too long
Why do SOME users' computers on my network take an unusual amount of time to log onto the network? I'm talking 15-20 min long! Not all of them too! This all started when we brought our server to our Austin office, due to Hurricane Rita, and brought it back to the gulf coast. When we brought it to Austin, we had to change settings (don't know what settings); then once we came back... there are some users who take forever to get on the server. Please help. Thank you, -- lfheb Have you checked event logs on both the client and the server? There has to be something in t...

Importing a Lotus file with a *.123 extension
My company uses Excel 97 and 2000 and we've received a Lotus file with a *.123 extension. Excel will not allow me to open it as it does not have the *.wk? format. I searched the web and while I found numerous people with the same problem I found no one who had the conversion answer. Any help would be greatly appreciated. Thanks Find someone with Lotus ver 9 or higher. Have them open the file, and save it as *.wk4 If you dont have Lotus available and want you can send it to me and I will do it for you Thanks "jim" <jimsto@gorbel.com> wrote in message news:088d01c35...

formula to copy data to empty cell
Using Excel 2003. Creating a client list, I want to copy home address to mailing address if mailing address is blank. For example, C2 is home address, L2 is mailing address. Thanks in advance. Just use a helper col, say col M Put in M2: =IF(L2="",C2,L2) Copy M2 down as far as required. Then copy col M, overwrite col L with a paste special as values. Clean up by clearing col M -- Max Singapore http://savefile.com/projects/236895 Downloads:18,100 Files:360 Subscribers:56 xdemechanik --- "Olchannel" wrote: > Using Excel 2003. Creating a client list, I want to copy hom...

parsing data
Hello; I have two data string types that I am doing a search on. ex:1 """ACCESS, NON-STANDARD SET-UP CLEAN""" I would like to strip all of the quotes from the front and then the same number of quotes from the back of the string so that it looks like ACCESS, NON-STANDARD SET-UP CLEAN How can I do this, since the number of quotes may vary between search string. also ex:2 """FITTING ALLOW 0.75""""/EA""" Note that in ex:2 should look like FITTING ALLOW 0.75" /EA Thanks for your time and effort in this.....

ICS File?
Hi, I want to generate a ICS file which contains data to create a task in my outlook 2003. Can somebody help me to find out more about those ICS files? What content they need? TIA Heidi It's fully documented in this RFC http://www.ietf.org/rfc/rfc2445.txt Probably eassier would be to create an appointment and save it as *.ics and look what's inside with Notepad -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -What do the Outlook Icons Mean? -Create an Office 2003 CD slipstreamed with Service Pack 1 ----- "Heidi Anselstetter" <smilingfr...

Exiting file without Excel default interfering
Hi, I would like to know if it is possible to insert some kind of code somehow and somewhere to make the closing of a file via anything other than a VB macro possible. That is I need to be able to stop a user closing a file via the X button or via the file menu, etc., and only close/save, etc., via a VBform that I create. I realise the Application.DisplayAlerts = False might do this?? but it doesn't seem to always work, particularly in Office 97. Rob I don't work with VB, but you might want to play with this idea: In the workbook that you want to control in the Thisworkbook module...

Detect Data Changed State in Row with Focus
On a continuous form, how do I detect that the row having focus has had one or more controls' data changed? A procedure started by a command button needs to know whether the row with focus has been changed by the user. This is before the focus leaves the row so the before update event for the row has not fired. It is after the before update events for a control would fire if present. Is there a simple way to test the form for the update state of the row with focus? I want to avoid a lot of VBA to collect the state change for each control in before update events. I just want to...

How long will it take for migration?
Hi, I plan to do migration SBS 2003 to SBS 2008. I have about 500 GB data on the source server. and I have gigabyte network. How long it will take for the migration? Will the migration, will users feel the slow open files on server? Will the migration use a lot of CPU or memory? Thanks in advance! Lisa Hi Lisa (again <g>) The acutal moving over of the data files is trivial. It will take a few minutes to an hour. The real time consumer here is the moving of the exchange mail and public folders, which can take days. If you want/need a more accurate estimate of ...

Allen Browne's Has the Rcd been Printed
Hi, I'm using Allen Browne's code from Has the Rcd been Printed and all of that is working great. I'm now trying to implement the Taking it Further - Track each time a record is printed. (This has been cross posted to queries. I only did this because there is only one day left on this forum.) I am trying to write an SQL statement that will Copy the keys from member table to member print audit table and set a value in the member print audit table. Here is SQL like statement that I want to do: INSERT INTO tblBatchMember (AcctNo, SET BatchID = 999) SELECT q...