Formula to skip value if sheet is missing

[cornepiek]

Hi all.

i need some assistance with a funny one.
i need two formulas, 1 to calculate average, and another to calculate the sum of the same field over multiple sheets.
ie: =Datasheet!B1+Datasheet(2)!B1+Datasheet(3)!B1

However, the data sheets i work with do not always contain the same amount of sheets.

so if i only had 2 sheets in this case, and used the same formula, the resulting formula would end up as
=Sheet1!B1+Sheet2!B1+REF
since sheet 3 does not exist.
also, if i had 4 sheets, then only the first 3 sheets will be included in the formulas.

the sheet names will always start with the same word ("Datasheet" in this case) with the sheets numbered in multipled as above.


i need this formula in a VBA macro in order to simplify my agents work.

Please help

thanks

 

[energman58]

This thread should help

http://www.mrexcel.com/forum/showthread.php?t=28731