bringing G1 value from 50 sheets to another sheet. Indirect comman
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 |
bringing G1 value from 50 sheets to another sheet. Indirect comman
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 |
bringing G1 value from 50 sheets to another sheet. Indirect comman
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 |
bringing G1 value from 50 sheets to another sheet. Indirect comman
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 |
bringing G1 value from 50 sheets to another sheet. Indirect co
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 |
bringing G1 value from 50 sheets to another sheet. Indirect co
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 |
All times are GMT +1. The time now is 10:53 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com