Working with many worksheet and pick up data!

  • Thread starter Thread starter WF
  • Start date Start date
W

WF

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







– Vis sitert tekst –
Click to expand...

"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 =FORSKYVNING(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.0121397545506105}
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 :-)
 
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







– Vis sitert tekst –
Click to expand...

"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 =FORSKYVNING(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.0121397545506105}
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 :-)
 
Back
Top