Automatically fill a cell based only on a non-zero value in another cell

[l1011driver]

Thanks in advance for any help or advice.

I'm not sure how to include an Excel fragment in the post so I'll do it the old fashioned way.

I have 4 columns. In column A are calendar dates. In column B are values of either 0, 1 or 2. These values are manually inserted by the user.

What I would like to do is this:

If column B is a non-zero value I would like Excel to insert the date of the corresponding cell in column A into column C. In column D I would like Excel to insert the non-zero value from column B. If the value in column B is 0 then I would like Excel to ignore both the 0 and the corresponding date.

For example:

A B C D

June 1 1 June 1 1
June 5 0 June 10 2
June 10 2
June 30 0

Is there a formula or a macro that will allow me to do this?

Thanks for any help or ideas.

 

[Nine Zero]

Hello,

Assuming your data starts at A1

in cell C1
=IF(B1<>0,A1,"")

in cell D1
=IF(B1<>0,B1,"")

then drag both down, make sure your column C is formatted as date and column D as General or Number

 

[l1011driver]

Hello,

Assuming your data starts at A1

in cell C1
=IF(B1<>0,A1,"")

in cell D1
=IF(B1<>0,B1,"")

then drag both down, make sure your column C is formatted as date and column D as General or Number

Hi Nine Zero,

Thank you for responding.

I went down this road, but here's the problem: by using this formula the "C" and "D" cells next to the "B" cells that are zero value will be blank.

What I want is the "C" and "D" cells to fill sequentially in their columns. For example, using the numbers from above I would want the date of June 1 to appear in C1 and the value 1 to appear in D1. Then I would want the date June 15 to appear in C2 and the value 2 to appear in D2. I want to eliminate blank cells in columns C and D.

I couldn't figure out any way to do that just using an Excel formula. Maybe a macro is the only way to do it, but I wouldn't have any idea how to come up with it.

Any thoughts?

Thanks so much...

L1011driver

 

[My Aswer Is This]

Try this:
Was not sure what you wanted to happen if column B had a 5 for example and then later was change to 0 or less so I entered last line of code which would then clear column C and D if this happened. If you don't want that then remove line of code marked in red.


This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B:B")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If IsNumeric(Target.Value) And Target.Value > 0 Then
Target.Offset(, 1).Value = Target.Offset(, -1).Value
Target.Offset(, 2).Value = Target.Value
End If
[COLOR=#ff0000]If Target.Value < 1 Then Target.Offset(, 1).ClearContents: Target.Offset(, 2).ClearContents[/COLOR]
End If
End Sub

 

[l1011driver]

Hi My Answer Is This,

Appreciate the code you sent. It worked fine, but I'm still getting blank cells in columns C and D adjacent to column B cells that have a value of zero.

Maybe the pic below will help you visualize how I want my spreadsheet to look. Is there any way to do this? I don't want any blank cells in columns C and D.




-- removed inline image ---


Any thoughts or ideas you might have would be appreciated.

 

[l1011driver]

Ooops...looks like my copy-and-paste didn't work.

 

[My Aswer Is This]

Are you wanting this script to run each time you enter a value in column "B" and just react to the row you entered a value in or are you wanting to run and check all rows in the sheet when you press a button.

This script only activates when you enter s value in column B and only effects that row.

And you said:
"If column B is a non-zero value"


But then you gave example like

1 or 2

so it 1 or 2 or non zero

 

[l1011driver]

Hi My Answer Is This,

Thanks for your reply. I know that my inability to figure out how to paste an Excel screen shot is making it more difficult. A picture is worth a thousand words. I apologize for confusing you.

Yes, I want the script to run only when entering a value in column B. The values inserted into column B won't change later.

Let me see if I can more clearly describe what I'm looking for.

A1 - enter a date. B1 - enter a non-zero value. Upon entering the non-zero value in B1 I would like the date from A1 to load into C1. I would like the non-zero value entered in B1 to load into D1.

A2 - enter a date. B2 - enter a value of zero. Nothing would happen in cells C2 or D2.

A3 - enter a date. B3 - enter a non-zero value. Upon entering the non-zero value in B3 I would like the date from A3 to load into C2. I would like the non-zero value entered into B3 to load into D2.

Maybe this will help a bit more. I'll use the example from my original post. Maybe I can describe what I'd like to do a bit better:

Column A has 4 dates, manually entered into cells A1 through A4. Column B has 4 values of either 0, 1 or 2 manually entered into cells B1 through B4. Two (2) of the values entered into column B have non-zero values, those being cells B1 and B3. Cells B1 and B3 have values of 1 and 2, respectively. I would like for columns C and D to display the date and non-zero value in cells C1 through C2 and D1 through D2. So in this example cell C1 would load the date from cell A1 and cell D1 would load the value from cell B1. The program will now look for the next pair of column A and B cells that have a non-zero value in column B. In my example the next pair to meet that criteria are cells A3 and B3. The program will load the date and value from cells A3 and B3 into cells C2 and D2. Columns C and D would continued in this fashion, with no blank cells between.

Oh man...only a thousand words? Haha....

I hope this helps you better visualize what I'm looking for. I appreciate your patience and trying to help.

 

[My Aswer Is This]

In your original post you said this:
"If column B is a non-zero value I would like Excel to insert the date of the corresponding cell in column A into column C. In column D I would like Excel to insert the non-zero value from column B. If the value in column B is 0 then I would like Excel to ignore both the 0 and the corresponding date."

You said "corresponding cell" which I would think means cell on same row.

A1 would go to C1 for example

But in your last post you said:

I would like the date from A3 to load into C2. I would like the non-zero value entered into B3 to load into D2.

This is not "corresponding cell"

And I would like for you to define "Non Zero"

I asked you this is last post and you did not answer that question.

For example these are "non-Zero"

#
$
*
drhjj
rrr


<strike></strike>

 

[l1011driver]

Hi My Answer Is This,

Thank you for continuing to try to help me. I apologize for the confusion I caused you between my first post and my last post.

When I used the term "corresponding cell" it was meant to refer to the cell in column A corresponding to the number value in column B, i.e. the "corresponding cell" for cell B2 would be cell A2, the corresponding cell for B4 would be A4, etc. After reading that again now a couple days later I was confused myself. Sorry.

The description of how I want the cells to fill in my last post is correct. As far as non-zero values, I see your confusion. The values to be entered in column B are only to be 0 or positive integers of a value of 1, 2 or 3. No negative numbers, no characters, no numbers greater than 3. There will never be a value entered in a cell in column B that doesn't have a date associated with it in column A. If cell B15 has a number between 0 and 3, inclusive, in it there will be a date entry in cell A15.

Hope this helps clear up things a bit. I apologize for my earlier lack of clarity of description.

Appreciate your help and your patience.