Formula required to copy and paste formula from 200 sheets in Summary sheet

[vmohan1978]

Hi,

I am consolidating the summary of 100-200 projects, in a file there will be 100-200 worksheets naming 1,2,3,4....upto 100 and it may increase also.
I am preparing the summary sheet in that I want to the formula for copy and down instead of linking in each sheet.
Find below the summary sheet with formulas

Sl.No project number overall status Project Category Completion date
1 '1'!$G$2 '1'!$G$25
2 '2'!$G$2 '2'!$G$25
3 '3'!$G$2 '3'!$G$25
4 '4'!$G$2 '4'!$G$25
5

Is there any formula to copy and paste so that automatically updates summary sheet, without any code?. please let me know how to do it with formula and with VBA code

 

[GraH - Guido]

Moha, you can try this ... If reference to sheet number is in column A en titles are in row 1.
B2 = INDIRECT(A2&"!$G$2")

-> Note that indirect is a volatile function. Depending on the number of formulas used, this might slow down your workbook. Each update, filtering, etc will invoke "calculation".

For VBA: did you already use the search on the forum? This has been asked and solved many times. Also consider Power Query if available for you. Sounds ideal for what you want to do. And not at all too difficult.

 

[vmohan1978]

Hi , Grah,

Sorry for the later response. The formula is working perfectly, is it possible to hyperlink the cell C18, once i click the cell it should go to particular sheet inwhich the formula is there for ex. If i click C18 it should goto sheet1 and C19 it should go to sheet2 accordingly.

Moha, you can try this ... If reference to sheet number is in column A en titles are in row 1.
B2 = INDIRECT(A2&"!$G$2")

-> Note that indirect is a volatile function. Depending on the number of formulas used, this might slow down your workbook. Each update, filtering, etc will invoke "calculation".

For VBA: did you already use the search on the forum? This has been asked and solved many times. Also consider Power Query if available for you. Sounds ideal for what you want to do. And not at all too difficult.