Taking Certian Data only from 2 Spreadsheets

Hi All,

I have 39 Excel Spreadsheets & there are only certain figures i woul
like in each spreadsheet & they are all in the same Coloumn & row. 
would like these figures to go into another spreadsheet i have create
how can i do this?? Is it possible? Can i transfer them over into a ne
spreadsheet so within this new sheet i then can caculate them all. Woul
appreciate all the help.

Cheers
Wil

--
william444
-----------------------------------------------------------------------
william4444's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3399
View this thread: http://www.excelforum.com/showthread.php?threadid=54645

0
5/29/2006 2:56:40 PM
excel.newusers 15348 articles. 2 followers. Follow

2 Replies
805 Views

Similar Articles

[PageSpeed] 11

One way would be via using INDIRECT

Perhaps a simple example to illustrate ..

In Sheet4,

Suppose we list sheetnames across in B1:D1,
eg:  Sheet1, Sheet2, Sheet3

and we have the cell refs of interest listed down in A2:A3,
eg:  A1, A3

Then, we could put in B2: =INDIRECT("'" & B$1 & "'!" & $A2)
and copy B2 across & down to D3 ..

B2:D2 will return the same as the link formulas:
=Sheet1!A1, =Sheet2!A1, etc

B3:D3 will return the same as the link formulas:
=Sheet1!A3, =Sheet2!A3, etc

INDIRECT will resolve the concatenation of the sheetname and cell ref text
strings to return the results from the particular sheet and cell listed in
B1:D1, and in A2:A3.  So we could design the layout and define / change the
sheetnames and cell ref text strings in the header row/col to suit the
purpose.

And for a neater look, we could also suppress the display of "extraneous"
zeros in Sheet4 via clicking:
Tools > Options > View tab > Uncheck "Zero values" > OK
-- 
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"william4444" wrote:
> 
> Hi All,
> 
> I have 39 Excel Spreadsheets & there are only certain figures i would
> like in each spreadsheet & they are all in the same Coloumn & row. I
> would like these figures to go into another spreadsheet i have created
> how can i do this?? Is it possible? Can i transfer them over into a new
> spreadsheet so within this new sheet i then can caculate them all. Would
> appreciate all the help.
> 
> Cheers
> Will
> 
> 
> -- 
> william4444
> ------------------------------------------------------------------------
> william4444's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=33991
> View this thread: http://www.excelforum.com/showthread.php?threadid=546459
> 
> 
0
demechanik (4694)
5/29/2006 10:47:01 PM
A sample construct is available at:
http://www.savefile.com/files/5536280
Indirect_Taking_certain data from 2 sheets.xls
-- 
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

0
demechanik (4694)
6/5/2006 12:14:52 PM
Reply:

Similar Artilces:

Creating an Add-in for POS 2.0
I am trying to create an add-in for a Microsoft Dynamics POS 2.0 installation that does not have RMS installed. I have the RMS Customization Guide 1.0, but does not seem to apply. I thought the process would be similar enough that I could figure it out. Creating the COM Component seemed straight forward enough, however POS 2.0 does not have a dll named QSRules.dll. There are however both a Microsoft.Rms.BusinessRules.dll and a SORules.dll. The process for Invoking the COM Component must be different as well. While there is a Addins directory in the "Microsoft Dynamics - Point o...

outbound mail #2
Outbound mail from our exchange 2003 server is staying in the que. although exchange is constatly retrying connections they do not send?? any ideas as the messages keep growing. thanks, Bill Are the users getting any type of NDR? A few that come to mind: DNS configuration Antivirus Software SMTP Syncs "bill" <bill@discussions.microsoft.com> wrote in message news:F8EB7C39-179B-4462-A1FB-A166E32AD1B1@microsoft.com... > Outbound mail from our exchange 2003 server is staying in the que. > although > exchange is constatly retrying connections they do not send?? &g...

Does anyone know of a software application for Pivot tables other. #2
We have some very complex pivot tables in Excel that unfortunately are not very stable or flexible when you make changes. The data list is used to generate multiple pivot tables in the same workbook but the data and the number of records can change frequently. One time we might have 18000 records and the next time we might have 2500 records. So far it seems that everytime we have to change the data we have to reset the data source range for each pivot table. I believe each pivot table was copied via Edit, Move or Copy, rather than being generated as anew pivot table or based on anot...

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

Keyboard to Start List or Insert Heading 1 or 2?
Word 2002 Are there keyboard shortcuts to insert a bulleted list and/or to format to Heasing 1 or Heading 2 style? Alternatively, is there some way to program my own keys to do this. Thanks, You can use the built-in Ctrl+Shift+L shortcut to apply a bulleted list (it actually applies the built-in List Bullet style). On my system, Alt+Shift+1 applies Heading 1, and Alt+Shift+2 applies Heading 2, but this may not be universal. You can assign the shortcut(s) that you want in the Customize Keyboard dialog box. To display the dialog, click Tools | Customize, and then click t...

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

Problem replying to emails #2
Hi all, I have imported emails/address book from Outlook Express into Outlook and when I hit reply to any emails that were NOT in my address book in Outlook Express I cannot send the message as it does not find the email address like Outlook usually does. Is there any way around this or would I have to manually add an email address to each contact? Thanks Scott Ashton <wibble@wobble.com> wrote: > I have imported emails/address book from Outlook Express into Outlook > and when I hit reply to any emails that were NOT in my address book > in Outlook Express I cannot sen...

Switching Licnese in rms 2.0
I got a new CPU and wanted to switch the License from Old to new. To do this you have to To activate Retail Management System Point of Sale (RMS POS) by calling the Activation Team (1 866 542 3062) - and run through a Battery of email menues. Is there not an easier way? Way make this soooo difficult? Just curious.. How long a process was it? Dan "Brent" <sagemaster@mvdsl.com> wrote in message news:OJNRnc6pHHA.196@TK2MSFTNGP05.phx.gbl... >I got a new CPU and wanted to switch the License from Old to new. To do >this you have to To activate Retail Management S...

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

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

2 Exchange Servers
I'm trying to test a configuration of 2 exchange 2003 servers. Each in a different domain 1 a child of the other and both in the same exchange org and administrative group both serving 1 mail domain. Each exchange server seems to be running okay but with default configuration when I try to send mail from a client I get an NDR (5.3.5) on that client's hosts exchange server. I'd like to see this pattern for outgoing/ incoming mail client in child domain--->Child domain exchange server--->Parent domain exchange server--->Outside world client in child domain<---Child d...

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

MS Outlook Installation Problem #2
I have installed MS Outlook from my Office 2003 CD. When I open the program up, I cannot get to the Calender. I go to the VIEW menu and I still cannot get to the Calender. I want to use Outlook vice Outlook Express on my home computer. Any suggestions of what I may be doing wrong that will allow me to see the calender?? ...

Splitting 1 cell w/2 words into 2 separate cells
Hey everyone! I have a listing of fantasy football players listed as (Firstname Lastname) in one cell. How can I split them up, so the first name is in one cell, and the last name in another? I need this so I can then sort by last name. Thank you! Gary Gary, Select the column, then use Data - Text to columns. Specify a space as the delimiter. Ensure that the next column is currently empty first. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Gary Hopkins" <garyh@somewhereoutthere.com> wrote in message news:41...

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

Top 2 from count
Hi! i have a table "tickets" and i need the top 2 most visited stores for every customer. create table #Tickets(ID int primary key, Store int, Customer int) insert #Tickets select 1,1,1 union all select 2,1,1 union all select 3,2,1 union all select 4,1,1 union all select 5,2,1 union all select 6,3,2 union all select 7,3,2 union all select 8,3,2 union all select 9,2,2 union all select 10,2,2 union all select 11,2,2 union all select 12,1,2 union all select 13,1,2 union all select 14,1,3 union all select 15,1,3 union all select 16,1,3 union all select 17,3,3 u...

How to see list or names of the macros in a spreadsheet
Recently, when I open a spreadsheet (that I use daily) I'm getting an Excel window/message stating that the spreadsheet contains macros and asking me whether I want to disable or enable them. I haven't created any macros, but I have pasted in text from various web sites, so possibly a macro has come with the paste operation. How can I find a listing of macros in this spreadsheet so I can troubleshoot this problem. When I go to Tools/Macro/Macros, the window opens up with a "macro name" but the window is of fixed size and since the spreadsheet name is long I can't read ...

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

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