Double Letter reference error

[jespo1351]

Hello!

I have a formula--> =C70-((INDIRECT(CHAR(64+COLUMN(D$1))&MATCH("ITEM 1",$C:$C,0)))*36*$B70)-((INDIRECT(CHAR(64+COLUMN(D$1))&MATCH("ITEM 40",$C:$C,0)))*36*$B70)

This works perfectly until I copy it over in the column AA, then I get a cell reference error...The formula in that field reads ---> =Z70-((INDIRECT(CHAR(64+COLUMN(AA$1))&MATCH("ITEM 1",$C:$C,0)))*36*$B70)-((INDIRECT(CHAR(64+COLUMN(AA$1))&MATCH("ITEM 40",$C:$C,0)))*36*$B70)

Is there a way to fix this in my formula OR is it just something that excel cannot handle? This formula essential takes a sales forecast from a particular month in a long list of items, pulls the to item values out for that month and multiplies them by their inner counts and subtracts them from the previous months total.

Thanks!

[jespo1351]

Here is a general screen shot of what I am trying to accomplish, If it makes and sense to you
excel help AA.png

[XOR LX]

Hi,

Your problem is that trying to indirectly reference column letters using CHAR() runs into the issue that CHAR(91) is NOT "AA" but "[".

This sort of thing is often avoidable using INDEX and no indirection, though without seeing your sheet it's hard to offer a solution with that method. A similar alternative to your current formula, though without the above-mentioned limitation of CHAR, would be:

=Z70-((INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(AA$1),4),1,"")&MATCH("ITEM 1",$C:$C,0)))*36*$B70)-((INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(AA$1),4),1,"")&MATCH("ITEM 40",$C:$C,0)))*36*$B70)

though I have to stress that an INDEX-based solution, if possible, would be much preferable.

Regards

[Jonmo1]

To understand why it fails when dragged to column AA
We must first understand why it works for Column D

CHAR(64+COLUMN(D$1))
Column(D1) = 4
CHAR(64+4)
CHAR(68) = A

Then indirect builds a cell reference with that letter + the result of the Match.

As it's dragged right, Column(E1) = 5 Column(F1) = 6 etc...
When it gets to AA = 27
64+27 = 91
CHAR(91) = [

That doesn't make sense as a Column letter in a Cell reference, therfor a #REF! error results.


To resolve, I would replace both instances these parts
INDIRECT(CHAR(64+COLUMN(D$1))&MATCH("ITEM 1",$C:$C,0))
INDIRECT(CHAR(64+COLUMN(D$1))&MATCH("ITEM 4",$C:$C,0))

With
INDEX(D:D,MATCH("ITEM 1",$C:$C,0))
INDEX(D:D,MATCH("ITEM 4",$C:$C,0))

Furthermore, If you're dragging this formula for 20+ columns,
I would HIGHLY recommend you put the match functions in their own designated cells and refer to those in the formulas..

Say $AY70 and $AZ70 for example

So $AY70 = =MATCH("ITEM 1",$C:$C,0)
So $AZ70 = =MATCH("ITEM 4",$C:$C,0)

Then use this formula and drag right
=C70-((INDEX(D:D,$AY70))*36*$B70)-((INDEX(D:D,$AZ70))*36*$B70)