Is there a better way than this formula by using a vlookup

[helenshas]

I am currently using this formula to add each worksheet is there better way to do this so I do not miss any descriptions
'=+'Head Office 000'!C19+'Social HSE 01'!C19+'TL 02'!C19+'Proj 003'!C19+'CCG 04'!C19+'Evo & Craw 05'!C19+'LV & Craw 06'!C19+'LV 07'!C19+'CCG & Craw 08'!C19+'RSN AA 09'!C19+'Blank 10'!C19+'Far H Office 100'!C19+'Far CCG & Evo 101'!C19+'Spare 102'!C19+'RSN & AA 103'!C19+'LV 104'!C19+'Spare. 105'!C19+'Spare 106'!C19

As I have description then the actual and there are 30 descriptions and in the next column there is the actual, but the descriptions are not in the same place is there vlook up I can use to combine the spreadsheets

 

[etaf]

a couple of ways to do that
are all the sheets contigous in the spreadsheet

say Head office 000 - is the first
and
spare 106 is last

=SUM( 'Head Office 000':'Head Office 000'!C19)
as they are all using C19

and all inbetween

My example
=SUM('Start-Sheet:End-S'!A2)

Also =SUM(Sheet3!A2,'Start-Sheet'!A2,Sheet5!A2,Sheet7!A2)

Maybe an indirect - pulling all the sheet names from a table

 

[helenshas]

a couple of ways to do that
are all the sheets contigous in the spreadsheet

say Head office 000 - is the first
and
spare 106 is last

=SUM( 'Head Office 000':'Head Office 000'!C19)
as they are all using C19

and all inbetween

My example
=SUM('Start-Sheet:End-S'!A2)

Also =SUM(Sheet3!A2,'Start-Sheet'!A2,Sheet5!A2,Sheet7!A2)

Maybe an indirect - pulling all the sheet names from a table

Hi thanks for that but some of the items are on different rows so it maybe on c19 on one sheet but on another it might be on c23 then on another sheet c30 I thought a v lookup could do it on the description then add a h sheet

 

[etaf]

not sure how to simplify that formula , as it different cells
you can use SUM() but that still requires all the sheets to be entered

there is a way to use indirect , and list all the sheets within the sheet - But that still requires you to list them all
this sort of thing
sumproduct ( with indirect() )

Sum across dynamic sheet names if criteria met on summary sheet

Hi all, many attempts at this one but unable to solve. So I have workbook with a varying number of worksheets which are listed in column A, column B has a Yes or No option pulldown, what I am attempting to do is sum the values from A1 of each sheet if column B is Yes, if No then exclude from...

www.mrexcel.com