Range named using UDF and resultant problem using INDIRECT

[tbablue]

Hi,

My workbook is essentially a datatable and numerous sheets, each with two charts on it. The charts are simple, formatted identically - but are populated using dynamic named ranges. Everytime I add more sheets, I need to manually create more dynamic named ranges to control each new graph.



I've a UDF that returns the corresponding sheetname of any cell that doesn't contain an error.

Function SheetName(rCell As Range, Optional UseAsRef As Boolean) As String
Application.Volatile
If UseAsRef = True Then
SheetName = "'" & rCell.Parent.Name & "'!"
Else
SheetName = rCell.Parent.Name
End If
End Function


When I use it like this in $A$1...

="'"&SheetName($A$1)&"'!"&"$AA$22"

...it behaves just as I expect and returns something like this...

'CPD5237FP Top Level - Full'!$AA$22

...so far, so good!


I thought I could be clever and use it within the INDIRECT function, escaping the need to declare explicit sheetnames in named ranges for charting.

e.g.
=OFFSET(INDIRECT(" ' "&SheetName($A$1)&" '! "&"$AA$22"),0,0,20,1)

The idea being that my UDF would operate in place of an explicitly declared sheetname and because the chartsheets are essentially set-out and formatted identically, one dynamic formula could be made to work regardless of the sheet it resides on. Make sense !?

However, when I input this formula into the name manager (Excel 2007) it doesn't work as anticipated. For example, copy and pasting the exact formula above becomes the following formula when you hit Enter...

=OFFSET(INDIRECT("'"&SheetName('Actual Sheet name here'!$A$1)&"'!"&"$AA$22"),0,0,20,1)

I don't want the RED cell refs (and they don't feature when I just use my UDF outside of INDIRECT) - but INDIRECT seems to incorporate them into the formula.

Am I barking up the wrong tree? Is there any work around that means I don't have to re-specify new named ranges for every new sheetname and chart?

Hopefully, this makes sense. ANy assistance gratefully rec'd.

Cheers