update absolute reference everytime there is a 1 in adjacent column

testinglife

New Member
Joined
Apr 20, 2017
Messages
9
I am trying to find a way to have my formula update it's absolute refence to the current row whenever there is a 1 in the cell next to it. For example looking at the image I attached to this post. I want it to have an absolute reference starting at $B$1 because there is a 1 in A1. However at A5 there is a new 1 so I want the formula in C5 to update it's absolute reference to $C$5 now. That way I can just copy it down a massive list and have it do that accumulating sum feature up to the next 1 then stop and start it again for each different grouping. The intervals between the new 1's are not constant. There can be many or few rows between the next 1.

Thanks for the help in advance.
 

Attachments

  • EXCEL example.png
    EXCEL example.png
    34.2 KB · Views: 6

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
2,018
Office Version
  1. 2016
Platform
  1. Windows
Hi Testinglife,

If you just want to restart the count then how about this:

Testinglife.xlsx
ABC
111010
221424
33200214
4432232
514040
622868
733361
842760
953057
1065080
117333383
1212323
1321336
1433245
151333333
16244377
1734387
Sheet1
Cell Formulas
RangeFormula
C1C1=B1
C2:C17C2=IF(A2=1,B2,B2+B1)
 
Solution

testinglife

New Member
Joined
Apr 20, 2017
Messages
9
Hi Testinglife,

If you just want to restart the count then how about this:

Testinglife.xlsx
ABC
111010
221424
33200214
4432232
514040
622868
733361
842760
953057
1065080
117333383
1212323
1321336
1433245
151333333
16244377
1734387
Sheet1
Cell Formulas
RangeFormula
C1C1=B1
C2:C17C2=IF(A2=1,B2,B2+B1)
Thanks, That help alot. I used a version of that to make it work. I was originally hoping to get everything into one massive formula but I settled for doing it accross many columns and using your idea to make it work.
 

Forum statistics

Threads
1,144,567
Messages
5,725,028
Members
422,589
Latest member
JohnnyBravo1

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?

Disable AdBlock

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
Top