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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Khoshravan - 05 May 2006 05:07 GMT

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.

Signature

Rasoul Khoshravan Azar
Civil Engineer
Osaka, Japan

Reply to this Message

Pete_UK - 05 May 2006 10:09 GMT

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

Reply to this Message

Pete_UK - 05 May 2006 11:34 GMT

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

Reply to this Message

Khoshravan - 06 May 2006 09:01 GMT

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)

Signature

Rasoul Khoshravan Azar
Civil Engineer
Osaka, Japan

> 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
[quoted text clipped - 16 lines]
>
> Pete

Reply to this Message

Pete_UK - 07 May 2006 00:50 GMT

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

Reply to this Message

Dave Peterson - 05 May 2006 14:00 GMT

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)
[quoted text clipped - 7 lines]
> Civil Engineer
> Osaka, Japan

Signature

Dave Peterson

Reply to this Message