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.

--
Rasoul Khoshravan Azar
Civil Engineer
Osaka, Japan

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

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

You have another response at your other post.



Khoshravan wrote:

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.

--
Rasoul Khoshravan Azar
Civil Engineer
Osaka, Japan

--

Dave Peterson

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)
--
Rasoul Khoshravan Azar
Civil Engineer
Osaka, Japan


"Pete_UK" wrote:

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

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