Copy and paste a sum formula but change column reference to a specific column

[davidopoulos]

Hi all,

I'm looking for a shortcut or quicker method of copying and pasting a sum formula and changing the columns references in the formula...

I have workbooks that contains a sheet which is a Detailed Breakdown of costs and prices for estimating projects which can run to hundreds of lines of activities. In that workbook I also have a sheet which acts as the top level Summary of the project where the subactivities in the Detailed Breakdown are rolled up to a single line item. The Detailed Breakdown has numerous columns of cost and price information but the Summary sheet has just a few columns (see the attached simplified example). That means I need to enter formulas for each top level summary line to sum the Price and Cost. In the attached example that means that in the Summary tab the formula in Cell B2 is =SUM('Detailed Info'!B3:B7), but then I need to repeat that for Cell D2 =SUM('Detailed Info'!H3:H7) (or I copy across the formula in B2 and manually amend the column reference) but this is a very laborious process for hundreds of lines.

Surely there must be a formula or method whereby I can reference the formula in B2 but automatically change the column that is being referenced (similar to OFFSET) or something?

Apologies if the above doesn't clearly explain what I mean - I am happy to clarify.

Many thanks in advance for any advice anyone can offer.

[Estevaoba]

If you can add an end text (any text) in both tabs, below the last work package values, try this in D2 and copy down.

Please Login or Register  to view this content.

Please check file attached and let us know how it goes.

Good luck!

[davidopoulos]

Hi Estevaoba,

Thank you very much for your help! That looks like it will work - i'll apply it to a more complex spreadsheet to check it will still work.

I will let you know how it goes!