Display result where cell in a row populated

[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!!!

[JBeaucaire]

Enter this formula out to the right on Row 1 beyond the test range, and be sure to press CTRL-SHIFT-ENTER to activate the array. Just pressing ENTER will give an error:

=INDEX(A1:P1,MATCH(TRUE,A1:P1<>"",0))

[dnf999]

Thanks a lot!

HOw about if I want to incorporate an IF statement into the cell, so if no cells are populated in that row display "no value"

= IF(INDEX(A2:P2,MATCH(TRUE,A2:P2<>"",0)) < 0, "no value", NDEX(A2:P2,MATCH(TRUE,A2:P2<>"",0)))

it doesn't seem to work, with the above code.

Any help much appreciated!

Thanks!

[DonkeyOte]

try:

=IF(COUNTA(A2:P2),INDEX(A2:P2,1,MATCH(TRUE,A2:P2<>"",0)),"No Value")
(again as before you must use CTRL + SHIFT + ENTER)

[dnf999]

Hi Thanks for that formula.

I have tried it on a simple example and it works great. But then I try it on a large number of values (including on some calculation cells), and all I seem to get is #N/A.

Bascially, I want the Result Cell (B15) to return 229,281.04 (EB12), because row 10 does not contain a number.

Any ideas why the formula does not work?

I've attached the spreadsheet

THanks again!!!!

[DonkeyOte]

Nulls are not seen as blank by COUNTA.

Given your values will only ever be numeric if they exist change the COUNTA to COUNT (remember to set the array using CTRL + SHIFT + ENTER).

[JBeaucaire]

As a matter of habit, I use the IFERROR function from Excel 2007. Here is the simple formula:

=IFERROR(INDEX(A1:P1,MATCH(TRUE,A1:P1<>"",0)),"No Value")
That's still an array formula, CTRL-SHIFT-ENTER...

Are you using Excel 2007? If not, add the IFERROR function yourself, it's a MUST HAVE in my opinion.

Please Login or Register  to view this content.

Paste that into a standard Module (same place regular macros go).

Will that do it for you?