Return data from different sheets and variated cells Macro

[simpmark]

Hi,

I need to sum data from several sheets, where the values which should be returned variates. I have a "Total" sheet where I want to summerise forcast from different headers in every sheet in the file. The location of the forcast which should be retured to the sum sheet could be variating depending on how many accounts every header includes on the different sheets. What is consistent is the header which always start on A6 and the value that should be returned (forcast) always start in H6 in each sheet.

So for exampel "Staff" should be sumerised from all sheet "CC1000", "CC2000", "CC3000":s headers "Staff" into the sheet "Total", in D4. Likewise should all the other headers be summerised.

Could somebody please help me to find a VBA code in order to solve this?

I've attached an example, so you could understand my case.Exampel file.xlsx

Thanks!

[Bob Phillips]

Does this do it

=INDEX('CC1000'!$H:$H,MATCH(Total!$A2,'CC1000'!$A:$A,0))+INDEX('CC2000'!$H: $H,MATCH(Total!$A2,'CC2000'!$A:$A,0))+INDEX('CC3000'!$H:$H,MATCH(Total!$A2, 'CC3000'!$A:$A,0))

[fotodj]

Should work...

[simpmark]

Thanks for the formula, it worked! How about if I have about 20 different sheets and don't want to write a long formula, but I have all Sheet names in F1 and below. Is it possible to use some formula to refer to all different sheet names instead of typing them in?

Also another question. Sometimes some sheets will not include every header in column A, which mean I'll get "#Value!". Is there any way to solve this?