change from relative reference to absolute reference

Dear all big brothers and sisters,

May I ask is there any macro that can change the formula from relative reference to absoulte reference?

I have the one as the following, however, it can't be applied on the formula with sumproduct and sumif.

I have the formula like as =SUMPRODUCT(--(Table!$C$2:$C$20=A18),--(Table!$F$2:$F$20="others"),Table!$L$2:$L$20)+SUMPRODUCT(--(Table!$C$2:$C$20=A18),--(Table!$F$2:$F$20="others"),Table!$N$2:$N$20)

However, after I use the macro (select the range from B18 to C20), it changed to #value.

Please Login or Register  to view this content.

Appreciate your reply.

Thanks for your reply.

As I use the macro to change from relative to absolute reference, it changed to #VALUE!.

The formula like that =SUMPRODUCT(--(Table!$C$3:$C$20=$A4), --(Table!$F$3:$F$20=B$1), --(Table!$E$3:$E$20=B$2), (Table!$L$3:$L$20)+(Table!$N$3:$N$20))


I would like to have the macro that can change the formula in relative reference to absolute reference in the formula of sumif and sumproduct...

Thanks for your reply.

hi, ronlau, try to shorten the file name to 1 symbol, for example: 1.xls and check if everything is working as expected

Dear watersev,

May I ask where is the file? I am sorry that I can't find it. Thanks

Dear Watersev,

After I tried several times, it doesn't work. You mean you attached file in post 2, JBeaucaire attached file?

It still turns to #Value