If cell is blank use data from another cell 1

[Cpreston]

HI

I ahve a sheet that uses2 columns for mileage. Columns H and K

If H is blank then I want it to copy what is in K and if not blank then leave it as the figure it as.. I have tried the following formula but it just returns a 0 and not 841 for example.

=IF(H16="",K16,H16)
=IF(TRIM(H16) = "",K16,H16)
=IF(ISBLANK(H16),K16,H16)

Googled it a lot but cannot quite seem to find the right result. Any ideas please.

Thanks in advance.

 

[Cpreston]

It works if I use a separate column , I was trying to place the formula in the H column so it just filled in the blanks from K when there was one, is this possible?

Thanks

 

[Deniall]

Surely you got a "circular reference" warning?

 

[Andrzejek]

It works if I use a separate column" and then copy from the separate column into column H as values.

Unless you want to use a little VBA...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Cpreston]

Any ideas on what code for the VBA, just trying to make it as slick as possible without extra columns.

Thanks

 

[Andrzejek]

That all depends on how your data looks like.
Could you include the representative sample of your Excel worksheet (as an attachment here)

Assuming your data starts in row 2, and ends when column H AND column K has a [highlight #FCE94F]blank cell[/highlight],

Option Explicit

Sub Cpreston()
Dim intR As Integer

intR = 2[green]
'Column H is 8
'Column K is 11[/green]
Do While (Not IsEmpty(Cells(intR, 8))) Or (Not IsEmpty(Cells(intR, 11)))
    If IsEmpty(Cells(intR, 8)) Then
        Cells(intR, 8) = Cells(intR, 11)
    End If
    intR = intR + 1
Loop

End Sub

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Cpreston]

Hi

Attached is an example of the data, I will look at the code you sent

Many Thanks

 

[Cpreston]

Hi Andrzejek

I tired you code on the sample data I attached and it only did the first one it found the 835 on row 12. I expected it to do the whole column so would have populated row 27 to 29

Thanks

 

[SkipVought]

If it were me, I'd
1) add a column [tt]Final Mileage[/tt],
2) convert you table back to a Structured Table
3) use formula... [tt]=IF(ISBLANK([@FinishMileage]),[@TripMileage],[@FinishMileage])[/tt]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[Cpreston]

Yes makes sense to do it that way, thanks for the help

 

[Andrzejek]

You can also modify my code to:

...
Do While Not IsEmpty(Cells(intR, 1))
...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson