Finding the value of a cell above a cell that meets a condition in a column

[rufuxinix]

In an Excel column I need to find the value of a cell that is immediately above.
Example: If I have cell values that end in 5, I want to extract the value of the cell above them.

 

[rufuxinix]

Did you try Post #11

Physically count the visible characters and somewhere where you are sure you can enter a formula the "Len" formula.



If you want to, you can check with a macro also.

Sub Count_Characters()
Dim i As Long
    For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
        MsgBox "Cell " & Cells(i, 1).Address(0, 0) & " has " & Len(Cells(i, 1)) & " characters!" & vbLf & _
        Asc(Right(Cells(i, 1).Value, 1)) & "  (This should be 53 if the last characters is a 5!)"
    Next i
End Sub

It looks like your cells are not formatted the way they should be. The formula shows as text.

Ok, thanks, I will try it.

However, as is, I am getting a system message that the formula is incorrect? #14

 

[rufuxinix]

Ok, thanks, I will try it.

However, as is, I am getting a system message that the formula is incorrect? #14

Hi there, here goes. Entered it is B2

 

[Joe4]

Works just fine for me:

Formula in cell B2 (and copied down for all rows).

=IF(RIGHT(RC[-1],1)="5",R[-1]C[-1],"Nothing")

I don't see how you could get the results you are showing.

How is column B formatted?
Is it formatted as "General"?

And do you have any VBA in this workbook?

 

[rufuxinix]

Works just fine for me:

View attachment 119979

Formula in cell B2 (and copied down for all rows).

=IF(RIGHT(RC[-1],1)="5",R[-1]C[-1],"Nothing")

I don't see how you could get the results you are showing.

How is column B formatted?
Is it formatted as "General"? YES

And do you have any VBA in this workbook? NO

 

[Joe4]

If you set up your simple example from post 22 above in a brand new Excel workbook, and add the formula shown in post 23, what do those results look like?

 

[rufuxinix]

If you set up your simple example from post 22 above in a brand new Excel workbook, and add the formula shown in post 23, what do those results look like?

 

[jolivanes]

So the third column takes formulae.
Select Column 3 and right click. Click on "Copy"
Now select Column 2, right click, select "Paste Special" and click on the "Format" radio button to select it. Click on OK.
Does that make Column2 accept formulae?

If not, use "DropBox" or a similar site to upload your workbook into so someone can have a look at it.
Make sure that personal data is changed before doing that.

 

[rufuxinix]

So the third column takes formulae.
Select Column 3 and right click. Click on "Copy"
Now select Column 2, right click, select "Paste Special" and click on the "Format" radio button to select it. Click on OK.
Does that make Column2 accept formulae?

If not, use "DropBox" or a similar site to upload your workbook into so someone can have a look at it.
Make sure that personal data is changed before doing that.

Column 2 takes formulae as well, the Len fn was there first and i just inserted a new column to include the IF fn.
Will do what you suggest, thanks.

 

[kvsrinivasamurthy]

try
=IF(RIGHT(TRIM(A2),1)="5",A1,"")

 

[rufuxinix]

try
=IF(RIGHT(TRIM(A2),1)="5",A1,"")

Thanks for your suggestion, but also doesn't work. This must be something to do with the Excel I have, for some reason it doesn't take these formulae.