Display first open column letter/row number

[rybussell]

Alrighty here...

I have a short little function that tells me the value of the first available row. IE if my sheet looks like this:

...A B C D E
1 X
2 X
3 X
4
5

It will return the number 4 because 4 is the first available number in the column that is blank. The formula looks like this:

Please Login or Register  to view this content.

So that works up and down... What if I want the letter of the first available column from left to right, whereas the above example would return the letter B? Is that possible?

Basically if I changed the code to:

Please Login or Register  to view this content.

It would display a B (or C if B was filled, etc)...

[martindwilson]

you could try
=ADDRESS(A1:X1,MATCH(TRUE,INDEX(ISBLANK(A1:AX1),0,0),0),2) but if you want to remove the cell number then
=LEFT(ADDRESS(A1:X1,MATCH(TRUE,INDEX(ISBLANK(A1:AX1),0,0),0),2),FIND("$",ADDRESS(A1:X1,MATCH(TRUE,INDEX(ISBLANK(A1:AX1),0,0),0),2))-1)

[:) Sixthsense :)]

Try this...

Please Login or Register  to view this content.

[rybussell]

@ SixthSense That returns me #N/A if everything is blank. If the value is a number, it remains #N/A.. If I put any text in, it only returns that text. So if my top row is 1,2,7,ELF, it would say ELF.. If my top row was 1,2,7,8,9,100, it would be #N/A.. Very weird...

@ martindwilson It works, but only if I have a value in cell J1 and the value has to be a number. It can't be text. If I have values all the way up to the I column, i get #Value... If I put any number in J1 above 0, it works. But J1 is the only cell it works in... So I can have a 1 in J1 and it will return A...

I hope I explained both of those correctly...

[martindwilson]

opps my mistake
=LEFT(ADDRESS(ROW(A1:X1),MATCH(TRUE,INDEX(ISBLANK(A1:AX1),0,0),0),2),FIND("$",ADDRESS(ROW(A1:X1),MATCH(TRUE,INDEX(ISBLANK(A1:AX1),0,0),0),2))-1)

or
=ADDRESS(ROW(A1:X1),MATCH(TRUE,INDEX(ISBLANK(A1:AX1),0,0),0),2)