How do I select & use contents of first non-blank cell in row?
Hi Pam
Forget making the range equal to the whole of row 2, row 3 etc., unless the
formula is going below the range you are concerned with.
I did my testing below, the used range, but of course if you put the
formulae to the right of your used range, then using whole rows will give
you a Circular Reference error.
Make the range whatever you wish, not necessarily starting at column A, but
ending before the column in which you place the formula.
Regards
Roger Govier
Roger Govier wrote:
Hi Pam
You can alter the range A2:L2 to whatever you want, but make sure that
you make both ranges the same. I made it that range, because I thought
your data was in columns A to L.
Try making it the whole row
=INDEX($1:$1,MIN(IF(2:2<"",COLUMN(2:2),"")))
Note I have made the Index row absolute with the $ signs, so you can
copy down to find the relevant month for each row of data.
Regards
Roger Govier
plf100 wrote:
Hurrah - we have lift off! Thanks Roger.
Just to note though, that this only works if the data starts in Column
A but its easy enough to reorder my columns.
Thanks again.
Pam
"Roger Govier" wrote:
Hi Pam
We'll get there in the end!!!
Just change from = to <
(=INDEX(1:1,MIN(IF(A2:L2<"",COLUMN(A2:L2),"")))}
again array entered initially and if edited.
Regards
Roger Govier
plf100 wrote:
I really appreciate your help Roger and I dont want to be a pest but
that formula seems to identify the first blank cell rather than the
first non-blank cell. Sure its a combination of both that you've
provided but think I'm now too close to it to figure out the right
combination!
Many thanks,
Pam
"Roger Govier" wrote:
Hi Pam
Sorry for the delay, I had to go out for a while.
I didn't read your post properly, and, as you found I gave you the
column for last non-blank cell.
I have been tinkering since, and there may be better solutions, but
the following array formula seems to work for me. Commit with
Ctrl+Shift+Enter, for the initial entry and any subsequent editing,
not just Enter. Excel will insert the curly braces { } around the
formula, don't input them yourself.
(=INDEX(1:1,MIN(IF(A2:L2="",COLUMN(A2:L2),"")))}
Regards
Roger Govier
plf100 wrote:
Thanks Roger, its exactly what I'm looking for. However, it seems
to be returning the value from the last non-blank cell in the row
rather than the first. Should I be changing the 9.99... value?
Here's an example:
Col A Col B Col C Col D
Row 1 APR MAY JUN
Row 2 1 3 MAY
Row 3 7 JUN
Row 4 34 APR
Kind regards,
Pam
"Roger Govier" wrote:
Hi
One way (as you say the data in row 2 is numeric or blank)
=INDEX(1:1,MATCH(9.99999999999999E+302,2:2))
Regards
Roger Govier
plf100 wrote:
I've got 12 columns (headed Jan - Dec), each of which contains a
combination of numerical & blank cells. For each row, I want to
select the first non-blank cell and return the column header
that it lies in e.g. Row 1, first non-blank cell is in the Apr
column, so I want the text "Apr" to be returned to another cell.
Help please.
|