to change cell address in the formula on cell value change

[abdulncr]

Dear Friends,

I have below formula.i need to change the cell address AK in the below formula on the value change in the A3.
=SUMIF(Working!$D$6:$D$3000,Report!B3,Working!$AK$6:$AK$3000)

for example if A3 is having value AZ i need the formula changed automatically as below

=SUMIF(Working!$D$6:$D$3000,Report!B3,Working!$AZ$6:$AZ$3000)

Appreciated any help.

Thanks
Abdul

 

[Sixthsense]

Try this...

=SUMIF(Working!$D$6:$D$3000,Report!B3,INDIRECT("'Working'!$"&A2&"$6:$"&A2&"$3000"))

 

[jim may]

Why not a simple If then else..?
=If(A3="AZ",SUMIF(Working!$D$6:$D$3000,Report!B3,Working!$AZ$6:$AZ$3000),SUMIF(Working!$D$6:$D$3000,Report!B3,Working!$AK$6:$AK$3000))

 

[abdulncr]

Try this...

=SUMIF(Working!$D$6:$D$3000,Report!B3,INDIRECT("'Working'!$"&A2&"$6:$"&A2&"$3000"))

Hi,

You really have sixth sense with good functioning. thank you so much it worked for me.

 

[Sixthsense]

Glad it helps you and thanks for the feedback