Actually, this is a text string, which is being used as an INDIRECT reference
for an INDEX to go and find a named range. Specifically:

Named range which it should be finding is MCB_1BR_MCC_JohnSmith2004Dollar.
This is a created name from what was a concatenated field of the following:

cell A19 which had "MCB (1BR, MCC)
cell B19 which had "John Smith"
cell D19 which had 2004
the word 'Dollar'

The formula for the cell which was used to name the range was
SUBSTITUTE(A19," ","")&SUBSTITUTE(B19," ","")&D19&"Dollars"

So I got rid of the spaces when making the name from which to create the
cell, and I guess Excel on its own changes parentheses and commas to
underscores when naming.

The place that looks for this named range is the following formula:

INDEX(INDIRECT(substitute(SUBSTITUTE(SUBSTITUTE(SU BSTITUTE($A3,"
",""),"(","_"),")","_"),",","_")&SUBSTITUTE($B 3," ","")&D$2&"Dollars"),,2)

in which A3 was supposed to be "MC B (1BR, MCC)"

So I intended for the formula to pretend the parentheses were underscores,
as well as the commas, and just delete the spaces. The problem is it seems
to give me an error on trying to type this. The odd thing is that this Index
is the exact same - in terms of what's written - as another one, which works.
This one is part of a much larger formula:

IF(ActualEnd<12,SUM(SUM(INDEX(INDIRECT(substitute( SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A3,"
",""),"(","_"),")","_"),",","_")&SUBSTITUTE($B 3,"
","")&D$2&"Dollars"),,2):INDEX(INDIRECT(SUBSTITUTE (SUBSTITUTE(SUBSTITUTE($A3,"
",""),"(","_"),")","_")&SUBSTITUTE($B3,"
","")&D$2&"Dollars"),,ActualEnd+1)),SUM(INDEX(INDI RECT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A3,"
",""),"(","_"),")","_")&SUBSTITUTE($B3,"
","")&C$2&"Dollars"),,ActualEnd+2):INDEX(INDIRECT( SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A3,"
",""),"(","_"),")","_")&SUBSTITUTE($B3,"
","")&D$2&"Dollars"),,13))),SUM(INDIRECT(SUBSTITUT E(SUBSTITUTE(SUBSTITUTE($A3,"
",""),"(","_"),")","_")&SUBSTITUTE($B3,"
","")&D$2&"Dollars"),-INDEX(INDIRECT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A 3,"
",""),"(","_"),")","_")&SUBSTITUTE($B3," ","")&D$2&"Dollars"),,1)))

The above is supposed to say that if the month which is indicated as actuals
ending is less than December, the formula is supposed to come up with a
calculated total which is equal to the months preceding the actual month in
the array named after the current year (current year being in D2) and take
all the month values that are in the post-ActualEnd part of the previous year
(previous year is in C2).

The index part

INDEX(INDIRECT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SU BSTITUTE($A3,"
",""),"(","_"),")","_"),",","_")&SUBSTITUTE($B 3," ","")&C$2&"Dollars"),,14)

is the same as the entire contents of the previous cell, which calculates
the entire previous year. So I am not sure why that same index, as part of
the larger formula, is giving me errors.

Hope that makes sense.
--
Boris


"Sloth" wrote:

Try this. I think your trying to substitute a comma in a number which needs
an extra step. This works for text and numbers

=SUBSTITUTE(TEXT(A1,"0,000"), ",","_")

"BorisS" wrote:

how do I write in a substitution of a comma? SUBSTITUTE does not seem to
like it when I just type SUBSTITUTE(reference, ",","_"). I need it
substituted with an underscore.

Thanks.
--
Boris