Summation not working with concatenate function!

Hello,

I am trying to total the D columns from 4 to 99 on each worksheet in a workbook, there are over 100 worksheets with different names.

I am able to list all the worksheet name on a new worksheet for calculation, however, when I try to use the concatenate functions to add the reference worksheets to the formula, it always return 0.

Here is the example

if I type in the following it works ok =sum(sheet1!d4:d99), it returns the total.

However, if I use the concatenate function to save the value in a cell say a1 =concatenate("'" reference sheet "'" & "!D4:D99") then use = sum(a1), then it doesn't work, it returns zero.

Any help would be appreciated!

Thank you for your attention and kind assistance.

kam

Thank you! It works now. Again thanks for your professional advice!!