bringing G1 value from 50 sheets to another sheet. Indirect comman

[Khoshravan]

I have 50 sheets called run1 ... run50
In G1 of each sheat I have a number (number of rows G4:Gn, in that sheet)
I want to bring the valuse of G1 from all 50 sheets to another sheet
(summary Shheet).
Is it possible to do it by "indirect" command to copy the (run3!G$1$)
reference cell? I couldn't succeed.

 

[Pete_UK]

Do you want to add all the G1 values together to appear in one cell in
the summary sheet, or do you want to have a list in the summary sheet
of 50 different values for each run?

Pete

 

[Pete_UK]

On reflection, I think you probably want 50 different values, so you
can make use of the ROW( ) function to give you the increment for your
"run" sheets. Assuming you have some description in column A, and that
your first formula is in row 3 (to allow for headings etc), then enter
this in B3:

=INDIRECT("'run"&(ROW()-2)&"'!G$1")

and copy down for 50 rows. You want the expression in the middle to
evaluate to 1 for your first row, so if this is on row 5 then change it
to:

(ROW()-4)

The apostrophes are not strictly necessary if you do not have spaces in
the sheet names.

Hope this helps.

Pete

 

[Dave Peterson]

You have another response at your other post.

I have 50 sheets called run1 ... run50
In G1 of each sheat I have a number (number of rows G4:Gn, in that sheet)
I want to bring the valuse of G1 from all 50 sheets to another sheet
(summary Shheet).
Is it possible to do it by "indirect" command to copy the (run3!G$1$)
reference cell? I couldn't succeed.

 

[Khoshravan]

Dear Pete
The idea of using Row() function to produce reference address is great, anf
this is actually what I was looking for. So thank you very much for your pure
knowledge in this issue.
BTW I would like to learn Macro and automatize some of these works. Which
book or site do you recomend for start?
I have tried to learn Macro and I have the basic knowledge but my main
problem is how to change the address (or use relative address) when I want to
perform same Macro for different sheet (sheet run1 to run50)

 

[Pete_UK]

Thanks for the feedback.

The Newsgroups are an excellent source of information about all things
Excel, so you could keep re-visiting them to increase your knowledge.

David McRitchie's site at:

http://www.mvps.org/dmcritchie/excel/excel.htm

is often recommended to people who want to learn more about macros, and
there are many more - just do a Google search, or follow David's links.

Hope this helps.

Pete