Working with many worksheet and pick up data!

I have a excel workbook with many simular formated sheet but different
data. And like to pick the value in cell A1 from ten sheets. Then I
like to visualize them in one sheet as a vector or matrix of data (two
dimentional data) for to easely work with them`?

I know we do summarize them like SUM(sheet1:sheet10!A1) but like to
use the data as they are. "spread them out as a vector" and make
arrays of them if nessecarly.... Should be a lot of possibilities and
power here anyone who can help on the track here, then I would have
been very greatful?

Wiggo
0
gotfredMX (4)
5/11/2009 6:09:07 PM
excel 39879 articles. 2 followers. Follow

3 Replies
336 Views

Similar Articles

[PageSpeed] 28

Not quite understanding what format you desire but try this
sub displaya1()
for each ws in worksheets
ms=ms & "," & ws.range("a1")
next
msgbox ms
end sub
-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
"WF" <gotfredmx@gmail.com> wrote in message 
news:e3e79ca7-91a5-41dd-9d7c-966024a1c8f3@r34g2000vbi.googlegroups.com...
>I have a excel workbook with many simular formated sheet but different
> data. And like to pick the value in cell A1 from ten sheets. Then I
> like to visualize them in one sheet as a vector or matrix of data (two
> dimentional data) for to easely work with them`?
>
> I know we do summarize them like SUM(sheet1:sheet10!A1) but like to
> use the data as they are. "spread them out as a vector" and make
> arrays of them if nessecarly.... Should be a lot of possibilities and
> power here anyone who can help on the track here, then I would have
> been very greatful?
>
> Wiggo 

0
dguillett1 (2487)
5/12/2009 12:45:11 PM
On 12 Mai, 14:45, "Don Guillett" <dguille...@austin.rr.com> wrote:
> Not quite understanding what format you desire but try this
> sub displaya1()
> for each ws in worksheets
> ms=3Dms & "," & ws.range("a1")
> next
> msgbox ms
> end sub
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguille...@austin.rr.com"WF" <gotfre...@gmail.com> wrote in message
>
> news:e3e79ca7-91a5-41dd-9d7c-966024a1c8f3@r34g2000vbi.googlegroups.com...
>
>
>
> >I have a excel workbook with many simular formated sheet but different
> > data. And like to pick the value in cell A1 from ten sheets. Then I
> > like to visualize them in one sheet as a vector or matrix of data (two
> > dimentional data) for to easely work with them`?
>
> > I know we do summarize them like SUM(sheet1:sheet10!A1) but like to
> > use the data as they are. "spread them out as a vector" and make
> > arrays of them if nessecarly.... Should be a lot of possibilities and
> > power here anyone who can help on the track here, then I would have
> > been very greatful?
>
> > Wiggo=96 Skjul sitert tekst =96
>
> =96 Vis sitert tekst =96

"Number format"   0,00%

Thank You so much for trying! I have no programming skills in VB, but
do all my work in excel.
I may expained myself to pure, so here I will try again. Let's say we
have four sheets named A, B, C, D  and in A1:A10 each of them has data
I like to use.

My goal is: To transform the data to a matrix "heigth and width" from
"height and depth" and give this dataset a dynamic name and some
parameters. Then the data is not necessarly visual in any sheet but I
can use it in further calculations.

Just for to visualize the data in a spreadsheet I found a solution
today =3DFORSKYVNING(INDIREKTE(I14:L14&"!"&"J7:J12");0;0;nobs;1)
means OFFSET(INDIRECT())
But my problem is a typical excel problem, Excel become slow because
of all the data. So I start putting as much as possible away from the
sheet with dynamic named areas. And this I can not do ...
because in the formulaline it only shows the first row of data
{-0.0276697494192508;0.0599827046017104;-0.0096308930609613;-0.012139754550=
6105}
not the whole dataset?

How may I overcome this problem in Excel hopefully without any Visual
Basic if possible?

Hope this may help you see the challenge :-)
0
gotfredMX (4)
5/12/2009 5:14:28 PM
On 12 Mai, 14:45, "Don Guillett" <dguille...@austin.rr.com> wrote:
> Not quite understanding what format you desire but try this
> sub displaya1()
> for each ws in worksheets
> ms=3Dms & "," & ws.range("a1")
> next
> msgbox ms
> end sub
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguille...@austin.rr.com"WF" <gotfre...@gmail.com> wrote in message
>
> news:e3e79ca7-91a5-41dd-9d7c-966024a1c8f3@r34g2000vbi.googlegroups.com...
>
>
>
> >I have a excel workbook with many simular formated sheet but different
> > data. And like to pick the value in cell A1 from ten sheets. Then I
> > like to visualize them in one sheet as a vector or matrix of data (two
> > dimentional data) for to easely work with them`?
>
> > I know we do summarize them like SUM(sheet1:sheet10!A1) but like to
> > use the data as they are. "spread them out as a vector" and make
> > arrays of them if nessecarly.... Should be a lot of possibilities and
> > power here anyone who can help on the track here, then I would have
> > been very greatful?
>
> > Wiggo=96 Skjul sitert tekst =96
>
> =96 Vis sitert tekst =96

"Number format"   0,00%

Thank You so much for trying! I have no programming skills in VB, but
do all my work in excel.
I may expained myself to pure, so here I will try again. Let's say we
have four sheets named A, B, C, D  and in A1:A10 each of them has data
I like to use.

My goal is: To transform the data to a matrix "heigth and width" from
"height and depth" and give this dataset a dynamic name and some
parameters. Then the data is not necessarly visual in any sheet but I
can use it in further calculations.

Just for to visualize the data in a spreadsheet I found a solution
today =3DFORSKYVNING(INDIREKTE(I14:L14&"!"&"J7:J12");0;0;nobs;1)
means OFFSET(INDIRECT())
But my problem is a typical excel problem, Excel become slow because
of all the data. So I start putting as much as possible away from the
sheet with dynamic named areas. And this I can not do ...
because in the formulaline it only shows the first row of data
{-0.0276697494192508;0.0599827046017104;-0.0096308930609613;-0.012139754550=
6105}
not the whole dataset?

How may I overcome this problem in Excel hopefully without any Visual
Basic if possible?

Hope this may help you see the challenge :-)
0
gotfredMX (4)
5/12/2009 5:15:05 PM
Reply:

Similar Artilces:

error in importing data
Sorry the error is 0x8004032d ...

Re: Extracting Exchange User Data From AD 02-24-10
If you do not like scripting, you can try GAL Exporter or Fast User Manager & Reports from IMIBO - http://www.imibo.com > > "Ringholz, Blake" <bringholz@nospam.com> wrote in message > news:76AEFC2F-85A7-4666-8262-27FB0737D09A@microsoft.com... >> Hello All - >> >> I need to get an Excel Spreadsheet that lists everyone first name, last >> name, email address, job title, etc pulled from Active Directory. Is >> there an easy way to do this? >> >> Thanks, >> Blake > > > > > ...

Customize Outlook today does not work #5
I have read the replies about if MS update 813489 is install customize outlook today does not work. What if you do NOT have MS update 813489 install and it still does not work then what? Here is a good one for you. If you update to Office 2003 then it works. But if you install (not update) Office 2003 you can open customize outlook today, but you can not make or save any changes. Error "an activeX control on this page might be unsafe to.... I am having the same problem. Only update 813489 has not been loaded on my computer, because it is not relevant to my system. However, I did ...

Access attachments don't work anymore
Outlook 2000's security update makes it impossible to receive MS Access .mdb or .mde files. But I need to do this to earn a living as an Access developer! Other than have the sender rename the file there must be a simpler way to override the security patch. Outlook Express let's you turn off this feature but OL 2000 has no such setting. (The feature is basically worthless anyway - any hacker would know you just rename the file, put your hacker code in it and send it. Access will open ANY file, regardless of what you name it!!) See if the information on the following page help...

Excel, how do I get ALT F C to work the same as ALT F Enter C ?
The above is an example, but it aplies to any menu. Before, if I pressed say, ALT F, the drop down menu would appear and I could press, say, C and get to the submenu. Now I have to press Enter, before the C, which is a bother. Thanks. On my Windows 2000 version 5.00.2195 with Excel '97 and Excel 2003 the Alt F C still works. What version software / Excel are you using? -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=21059 View this thread: http://www....

Doing Analysis from large amounts of DATA
Hi all, I have an attachment that's apart of this message & within it I have 3 tabs 1) Revenue 2) Expenses & 3) Net Position. I would like to do some Analysis on the above three thou a way where I can manipulate the Data. How would I go about doing this? I've tried a Pivot Table thou it didn�t work out in the sense that it was messy & just failed in calculating stuff, not sure if I did it right thou.. Would really appreciate all the help as I actually have about 20 of these sheets which from that fall into 4 groups being 5 sheets per group. I hope this all makes sense, wo...

lost data when opening excel workbooks ; text import wizard popup
When opening many of my excel files ,which all have the same modification date, I come across the text import wizard which states that my text in these files is 'delimited'. All of the files ,including a few word doc.s have had their data changed to show all " y " with two dots above the letter for as far as the eye can see. No import or export has been done with the files and no modifications were done on that date, as far as I know. Is this a corruption problem or is their some 'fix' that I am overlooking. Thanks for any ideas. ...

Convert Column to row with variable data
Hi I'm using Excel 2k and I have a spreadsheet that looks like this Name Address Fred 21 Blah St London Sue Tower 50 London EC2 and need it to look like this Name Address 1 Address2 Address3 Fred 21 Blah St London Sue Tower 50 London EC2 I have a macro that can convert from column to row but only for a set number of columns. Is there any way to account for the variable amount of data for each address ? Any help much appreciated Thanks David David: I suggest the following formulae - copied down as necessary: C2: =IF(...

Do you need Access to use an Access Database for data entry only?
We have an Access database completed. Now we will use it for reading as well as for data entry. Do we need to install Access for every workstation needing the database for data entry or reading? No you don't necessarily need Access on every computer. You CAN use the Access Runtime and if it is in Access 2007 you can use it for free. If you are using another version then you would need to purchase the developer's edition to be able to legally distribute the associated Access runtime. If you do have 2007 and want to use the runtime, make sure your Full version of Access doe...

DPM sometimes uses too many tapes
Hi, i have problem with DPM long term backups on tape. I have collocation enabled so that is not a problem. Sometimes DPM uses too many tapes for same protection group, sometimes it uses 1, and then sometimes 2 - 4. Amount of data is always same. Can anybody help me please, what could be the problem? Hi Tornado, Can you explain protection group configurations as well as tape usage in detail, so that we can analyze on what is happening? Thanks, Jyothsna[MSFT] This posting is provided "AS IS" with no warranties, and confers no rights. "Tornado" <Torn...

Formulas don't work in certain cells #2
nope, the cells are formatted as numbers. I simply cannot figure thi out. -Jorda -- kalik24 ----------------------------------------------------------------------- kalik247's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1594 View this thread: http://www.excelforum.com/showthread.php?threadid=27423 Being formatted as numbers doesn't automatically mean they are numbers. Copy an empty cell formatted to General. Select your range of numbers and Paste Special>Add>OK>Esc Gord Dibben Excel MVP On Mon, 1 Nov 2004 11:59:46 -0600, kalik247 <kalik2...

Importing data into Outlook #2
Is there a process or program with which I can transfer my ACT! data into Outlook contacts? Thanks! ...

Odd problem with worksheets when opening file
Okay, here's the odd problem that's come up. When you double click on a excel file, excel opens up, but you can onl see the toolbars. The grid area looks like a snapshot of whatever you current background is before the file opened up. If you were showin your desktop, after the excel file opened, you'd still see your deskto in the datagrid area. If you start up a new excel file, then do the File>Open>file name, th file will open correctly. This problem happens across users here. Everyone is currently runnin office 2K. Any ideas on what causes the problem and any...

"File: send to mail recipient" not working
When users (on WinXP) select file, "send to mail recipient", no response from Outlook 2002. This occurs either when Outlook is open or closed. Problem happens when rt. clicking on .doc, .xls, .pdf's....all files. Any ideas?? Thanks!! ...

Insert Page Numbers on Worksheet in Excel 2007
In Excel 2003, if you wanted to put page numbers on multiple worksheets in a workbook, you grouped the worksheets and then added a header or footer, using the page number function. All of your grouped worksheets would shows its own page number. But in Excel 2007, only my first worksheet is numbered when I do this (as Page 1). What's the problem? Do I actually have to put a page number, one by one, on each worksheet? I cannot replicate your problem with 2007. Grouped sheets behave exactly as 2003 did. After grouping and adding a header of Page 1 did you do a print p...

How do I show a data table with legend keys in a line chart?
I am having trouble inserting a data table with legend keys into a line chart that contains a horizontal target line. Can anyone help me? Hi, I tried it in both 2007 and 2003 and had no problems. We need more infomation, can you post a sample of the chart somewhere. The only issue I have is that the target line is displayed on the data table, which I would prefer that it not be. -- Thanks, Shane Devenshire "excelbanker" wrote: > I am having trouble inserting a data table with legend keys into a line chart > that contains a horizontal target line. Can anyone he...

Can Work thread get a windows class and manipulate it?
Suppose that I create a work thread when my dialog box started, then in the thread I get the pointer to the dialog mfc class and call updateData method. Does that work? I tried, but failed. Is there any other way to do? thanks!! No, it won't work, don't even waste time trying. It is almost guaranteed to fail. You do not manipulate the windows owned by one thread from a different thread. What you do is PostMessage requests from the worker thread to the main UI thread, usually to the window that contains the controls. The fact that you are using UpdateData already says you are in t...

Subform doesn't work once added to main form
Hi, I've been searching around looking for some help and can't find anyone that has had quite the same problem. I have a main form called 'Sites' and have added a subform called 'Previous Year Flows' into the main form. I do not enter data into this subform. Instead, data on previous years is drawn through from a table called prev yr flows into this subform. When I open the subform on its own it draws through all the necessary data, but when it is embedded within the main form, none of the actual data is pulled through at all, although the linked fields, Site ID and A...

Output the list of frequent data
Dear all, Here comes two problems. Problem 1: I have a list of strings (say, in the column A1:A100). How can I find the "mode" (i.e. the string appearing most frequently in the list? For example, if the list is APPLE APPLE BANANA APPLE CREAM CREAM BANANA CREAM APPLE DONUT then I want the result is APPLE. It seems that the MODE function does not suppot data type other than numbers. Is there any canned UDF for it? Problem 2: Following Problem 1, I want to generate a list of the 3 most frequent data in the list. If the list is the one in the example, I want to list to be...

Excel Macro
I am writing a macro and cannot find any command that will allow me to move the cursor in the Worksheet. Rather than give a Cell address such as "A3" I want to be able to move the cursor any given number of cells to the right,left, up or down. Can anyone help? activecell.offset(x,y).select will move x rows down (if positive, up if negative) and y columns to the right (if positive, left if negative). Stephen King wrote: > > I am writing a macro and cannot find any command that will > allow me to move the cursor in the Worksheet. Rather than > give a Cell address ...

Data Validation #47
kshave multiple identacle sheets in a workbook(31 sheets). I am trying to allow users to only enter whole numbers in some of these cells. I know I can do it through Data /Validation/Allow - but, I want to do it on all 31 sheets at once. When I select all sheets - Validation is greyed out. Should I be using a different method? Thanks, Tom Apply the data validation on Sheet1 Copy the cell(s) in which you applied data validation Select Sheet2 Scroll to see sheet31 Hold the Shift key, and click on Sheet31 tab Select the cell where you want to paste the data validation Choose Edit>Paste Cl...

Modifing data to show up better in chart.
My Y1 axis is from 0-250 my Y2 axis is from 0-9000 When graphing percentages the line shows up at the very bottom since 99% < 1 is there a way I can multiply these percentages by 100 to show them as 9900% = 99 Normally you would just make then a 2nd axis but as you can see I already have a second axis of 0-9000 In article <A9E6F75B-7307-4291-BC7D-729F9DF88D50@microsoft.com>, MikePunko@discussions.microsoft.com says... > My Y1 axis is from 0-250 my Y2 axis is from 0-9000 > When graphing percentages the line shows up at the very bottom since 99% < 1 > is there a way I...

Access Outlook Add-in for Data Collection & Publishing won't load
I am trying to utilize the Collect Data via e-mail function and keep receiving the following error: "The Microsoft Office Access Outlook Add-in is disabled in Microsoft Office Outlook 2007. To collect data by using e-mail messages in Microsoft Access 2007, verify that this add-in is installed and enabled from within Office Outlook 2007." I have tried to remediate via the Trust Center/Add-in area in Outlook and don't see an add-in for Access (even though the error message says it is "disabled" it is nowhere to be found). I have tried to add it (I believe it is...

if cell in other worksheet meets criteria, then leave blank.
I have data in worksheet A for each month, for each entity. Then I have a summary page which shows just the averages for each entity for each month. Then I need to create a master summary page which shows just the annual average for each entity, just one line per entity. My Summary page shows Jan-Dec in column A. I have a formula averaging the numbers from worksheet A. Jan-Mar is done. But the rest of the year hasn't happened yet, so April's formula results "#DIV/0!". I have already put in all the formulas for the year to be done with it. But I don'...

Merge data from Acces to word
I have an Access db with records on a form which I want to export to a Word Template with predifened fields. How can I do that? reidarT Take a look at Albert D. Kallal's Super Easy Word Merge at http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html -- Hope this helps, Daniel Pineault "ReidarT" wrote: > I have an Access db with records on a form which I want to > export to a Word Template with predifened fields. > How can I do that? > reidarT ...