HELP! First populated value in row display

[dnf999]

Hi I have an urgent problem, I hope many of you can help me solve.

I need to display the result from a cell in a row, only where it is populated and where it is the first populated cell.

i.e.
Column A B C D E F G H
Row1: , , , , , , 20, 23, 45

Result: 20 (as 20 is the first populated number in row 1)

Please can you offer any suggestions on how I can do this, the results can change dependent on some variables, so my basic logic is to say the result is the first value of row 1 which is populated.

Also, I would appreciate if the problem could be solved using functions in the Excel formula bar, rather than any VBA code.

Many thanks!!!

 

[Bilbo_Baggins_Esq]

is the first populated number always going to be the lowest number?
If so, you could use =MIN(A1:H1) and put that in cell I1.

Presumably, it is not that simple, so I'll scratch my head and see if I can figure out a non-coded way to get the first populated column's value regardless of its rank (min or max).

 

[Bilbo_Baggins_Esq]

What version of Excel are you using?
Are there always columns A through H with potential values?
Are there any other variables (e.g. other blank cells after the first populated cell)?

 

[dnf999]

What version of Excel are you using?
Are there always columns A through H with potential values?
Are there any other variables (e.g. other blank cells after the first populated cell)?

Yes the number of columns is set.

No it is not always the lowest number. It is which ever cell is populated first in the row.

There can be other blank cells after the first populated cell. (Although very unlikely)

THanks so much!

 

[Bilbo_Baggins_Esq]

Ok, I admit it.
The Public Function is elluding me.

There needs to be a function and a supplied range in the formula, for example
=FirstValue(A1:H1)

Conceivbably, it need not be limited to only columns A through H, but all I keep getting is #NAME! errors.

Anybody else swifter than I when it comes to writing a Public Function for this challenge?

 

[dnf999]

Hi Bilbo

Thanks for taking a look. I was able to get some assistance, and came up with this:

=INDEX(A11,MATCH(TRUE,A11<>"",0))

(As it's an array, you need to use Ctrl+shift+Enter, for the formula to work)

This worked a treat

Thanks

 

[Bilbo_Baggins_Esq]

lol

I was originally looking at that, but couldn't get match right.

kewl, glad you got it worked out.