How to autofill a column depending on data in another column

[samashlit]

Hello,

I have 2 columns of data (only 1's and 0's).
When column A is "on" I would like it show the number 1 (and column B to display 0), and that until that number 1 appears in column B. From that point, column B is "on" and should display the number 1.

Please see the image and example worksheet attached.
I have tried several options, but none have worked so far (I am not proficient in excel). Presumably (or hopefully) there may be some easy trick or formula that can manage this.

Image columns.JPG


Thanks in advance for any help!

Sam,

[TMS]

Are you saying that what you have in column A is fixed/static? And that what appears in column B is the opposite 0 -> 1, 1 -> 0? Or are you saying you also have some (wrong) static values in column B.

If only column A is fixed, you could have a formula in column B to reverse the values.

Please Login or Register  to view this content.

For scenario 2, you would probably need VBA To convert the values in column B … or a Helper column that operates in the same way as scenario 1.

[JohnTopley]

To reverse values

=IF(A3,0,1)

[samashlit]

Thank you for your reply.

What I am trying to accomplish is more like scenario 2.

It would be some sort of "until" function or loop.

Column A should have the number 1, until that number appears in column B; and then the number 1 should appear in column B until again it appears in Column A, and so forth.

It is essentially like pushing two buttons.
If I push button A for 5 seconds, I would have 5 hits of the number "1" (in column A cells 1 to 5 would be filled). Then I start pushing button B (and column B cells 6 to 10 would have the number "1", while those cells in column A would have "0"). Then again I push button A...

Hope that may make it a bit more clear.
Thanks again for the help,

Sam

[JohnTopley]

You will need VBA to do this as you cannot have both a formula and value in a cell


.

Please Login or Register  to view this content.

[samashlit]

Thank you JohnTopley!

This is exactly what I need. Works perfectly