trying to use sheet CodeName to list all sheet names in a workbook

Basically I have a workbook with 53 sheets in it and I need to list those sheet names on a summary sheet. The problem is i need to use CodeName because the sheet order within the workbook WILL change and i need the summary sheet to stay the same, hence need to use CodeName.

I have numbers in column A which I want to use as the codename number and the formula in column b should return the sheet name for that codename numbered sheet.

I have a user function which does the job but this is not based on codename so whenever the order of the sheets change, so does the order of items in the summary sheet

Please Login or Register  to view this content.

Using that function, I have the sheet numbers 1-53 down column A, and "=SUM(INDIRECT("'"&$B4&"'!$C$449:$L$449"))" which uses the sheet name and sums a range from within each sheet.

I am sure its easily possible just modifying the function to use SheetNumber input for the codename number, I just havent managed to work it out, instead of Sheet1.name something like Sheet1SheetNumber.name

Please remember to use code tags when posting code to the forum.

Dom