summarising the data from various sheets

**srinivastsc** · 01-17-2013, 04:57 AM

dear friends!

I have an excel 2010 file which consist of 100 sheets of similar format with certain data. i want to summarise with relevant fields by using sheetname.

example : '11'!m18 like wise '15'!m18
like wise i have to link up the around 30 fileds of various sheets ( above sheet nos.are 11 and 15 and data fields are m18). cell address of summary is similar except the sheet name should change in the formula dynamically without manual intervention and that too by draging the previous formula to next cell.

do the needful, thanks in adv.

**Pete_UK** · 01-17-2013, 05:32 AM

If you have a list of the sheetnames in, say, column A, starting in A2, you can put this formula in B2:

=INDIRECT("'"&A2&"'!b18")

then copy down.

Hope this helps.

Pete

**srinivastsc** · 01-17-2013, 06:03 AM

thanks for early reply,but pl.chek my attachment to solve my problem.thnks

**Pete_UK** · 01-17-2013, 06:21 AM

Use this formula in B3:

=INDIRECT("'"&B$2&"'!B4")

and this one in B4:

=INDIRECT("'"&B$2&"'!B6")

Then you can copy them across for other sheets.

Hope this helps.

Pete