# to change cell address in the formula on cell value change

#### abdulncr

##### New Member
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

### Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

#### Sixthsense

##### Active Member
Try this...

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

#### jim may

##### Well-known Member
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

##### New Member
Try this...

Code:
``=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

##### Active Member
Glad it helps you and thanks for the feedback

Replies
12
Views
390
Replies
2
Views
100
Replies
5
Views
320
Replies
1
Views
130
Replies
9
Views
241

1,195,670
Messages
6,011,074
Members
441,581
Latest member
rp4717

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back