• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

One sheet as summary of values of +100 sheets

E

ExcelUKE

New Member
I use 1 workbook per month with +100 sheets using the same data fields different values, I would like to run a macro that would list the values in a single separate sheet as a summary. In other words a new sheet with each worksheet’s specific cell values listed in rows beneath each other, so that the monthly values can be totalled and reported.
 
Hi ExcelUKE


You do not need a macro to do that, if the structure of the Summary is static you can link the summary to your sheets.


If you want to make it more dynamic as the sheet names change or to copy the formula down all rows use the INDIRECT function for the Sheet Names in your formula
 
Hi ExcelUke,


Here is an example:


You have four sheets - Summary, One, Two, Three


You have Data in C10 of sheets One, Two & Three


On the summary Sheet - A1 - "One", A2 - "Two", A3 - "Three"

in column B insert the cell you want form these sheets, so B1, B2, B3 - "C2"


Insert the following formula in C1 =INDIRECT(A1&"!"&B1) and copy down


You know have the data from the sheets on the summary sheet
 
You must log in or register to reply here.
Back
Top