I have a project where I work a lot with dynamic names and ranges. I
have defined names such as the one below

TotalNumberOfRowsData=COUNTA(Rawdata!$B:$B)

I am referring to these ranges from the worksheet as well as from VBA.
Everything has worked like a charm, but now I want to make it more
generic. I have defined some names that are initiated at startup from
constants, which makes them easy to change. The constants refer to
which columns certain data can be found etc. I then use these
initiated names in the other defined names, by using the indirect
function in different ways (not very pretty if I may say so myself,
but the only way that I found to be working). By doing like this, I
get the option to get functions similar to the one below by just
changing two constants (the contstants for the sheetname and for the
column).

TotalNumberOfRowsData=COUNTA(OtherData!$C:$C)

This also works as long as I stay in the worksheet, but when I try to
access these names in VBA with the evaluate function, I get error 2023
(or totally incorrect answers).

The only explanation I can find is that the indirect function does not
work really well with evaluate, but I haven't found out how to change
it. Does anyone know of any way to come around this problem (it does
not need to be with the indirect function, it can be any other way) I
would be very grateful.

I have found a way by inserting an equal sign in front of the name,
but this only works when indirect is in the first position of the
name.

//Anders