Import Data from the different worksheet in the same excel file.

[hem3414]

-I would like to create a table.
-The table will have two columns.
-The data in each cell is a summation of a number of cells in another
worksheet.
-In my table, there are quite a few rows and hence I can't create this
table manually.
-I created the first value by writing the formula for summation and
clicking on each different cell values in another worksheet and tried
to copy the formula in the other cells for all the worksheets.
-Its not switching the worksheets automatically, it just switches the
column in the same worksheet.
-Below is the table I am trying to create.And I want to generate the
values automatically.
*Total*
Actual Planned
Sheet1 750 760
Sheet2 750 760
Sheet3 750 760
Sheet4
Sheet5
Sheet6
Sheet7
Sheet8
Sheet9
Sheet10

-The table below is the table I have in all different worksheets. I
need to do the total of each column which goes into one cell in the
previous table.

YTD*
Actual Planned
Entry1 95 90
Entry2 80 77
Entry3 90 65
Entry4 55 54
Entry5 40 65
Entry6 15 54
Entry7 0 0
Entry8 0 0
Entry9 0 0
Entry10 0 0

-hopefully somebody will be able to help..

Thanks,

 

[Otto Moehrbach]

If you don't want to manually put in all the formulas whenever the sheet
changes, the only alternative is to automate the process.
That raises the question: Do you want the summation in the cells of
your table or do you want the formulas in those cells? In other words, let's
say the summation in some cell is 345. Do you want just the number 345 in
the cell or do you want a formula that produces 345?
Creating automation requires the writing of code ( a macro). Writing
code requires detail knowledge of the layout of your data. Like, what cells
of what sheet do you want summed and where do you want the summation (the
answer) to appear? Be specific.
For instance:
Column A of the Summation sheet has a list of sheet names starting in A2
down.
For each sheet, the entry in Column B (of the summation sheet) will have the
sum of Column G of the respective sheet, from G2 to the last entry in Column
G.
And so forth.
HTH Otto

 

[hem3414]

Otto,

Thanks for replying. That is exactly what I want to do. Could you hel
me automating it? I mean the way you said I will have to write a smal
macro for that. Could you help me out with it? I will really reall
appreciate that.

Thanks,
Hemil.

 

[Otto Moehrbach]

Yes, I can help you with that, but I will need the information I asked you
for. Pretend I just walked in off the street, know nothing about what you
want to do, and you want to explain to me what you want me to do manually to
get what you want. HTH Otto