summary sheet across multiple sheets

[Drew]

Hi again Excel 2007 boffins.

I have multiple worksheets within a workbook each tab with custom names that
each contain specific contract data.

I have a summary page but all summary data is generated manually by my using
'=' then clicking specific wooksheet tab, clicking cell and 'enter'.

My data in the worksheets is not arranged by column headers.

Surely there is a formula likely built around vlookup that could make this
less manually intensive.

Thank you.

 

[Max]

Assuming your "child" individual contract sheets are identically structured
here's a fast n easy way to build a summary using INDIRECT ..

In the Summary sheet,

List the "child" sheetnames in B1 across, eg: Con1, Con2, etc
(sheetnames must tally exactly with what's on the tabs, except for case)

In A2 down, list the cell references to extract data from eg: B2, C3, etc

Then place this in B2:
=IF(COUNTA($A2,B$1)<2,"",INDIRECT("'"&B$1&"'!"&$A2))
Copy across & fill down to populate

 

[Drew]

Max ... thanks so very much.

After adjusting to transpose columns and rows plus position on summary page
it works beautifully.

 

[Max]

welcome, Drew