Hello, Could somebody please help me with the following


I have a row of numbers and the letter x. the numbers are always sequential
except when an x marks a non working day.

A B C D E F G H
I J K
ROW1 x 1 2 3 4 5 x x 6 7 8
ROW2

In Row 2 I would like to get the cell address of the previous cell that does
not have an x in Row1.

Eg. Cell I2 would return the address of cell F2
Cell D2 would return the address of cell C2

I am going to embed this cell address in another formula.

=ADDRESS(1,MAX(IF($A$1:H$1<"x",COLUMN(A:H))))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"ellebelle" wrote in message
...
Hello, Could somebody please help me with the following


I have a row of numbers and the letter x. the numbers are always
sequential
except when an x marks a non working day.

A B C D E F G H
I J K
ROW1 x 1 2 3 4 5 x x 6 7 8
ROW2

In Row 2 I would like to get the cell address of the previous cell that
does
not have an x in Row1.

Eg. Cell I2 would return the address of cell F2
Cell D2 would return the address of cell C2

I am going to embed this cell address in another formula.

sorry my mistake, i actually want the value in the cell that your formula
returns not the address as previously requested.

"Bob Phillips" wrote:

=ADDRESS(1,MAX(IF($A$1:H$1<"x",COLUMN(A:H))))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"ellebelle" wrote in message
...
Hello, Could somebody please help me with the following


I have a row of numbers and the letter x. the numbers are always
sequential
except when an x marks a non working day.

A B C D E F G H
I J K
ROW1 x 1 2 3 4 5 x x 6 7 8
ROW2

In Row 2 I would like to get the cell address of the previous cell that
does
not have an x in Row1.

Eg. Cell I2 would return the address of cell F2
Cell D2 would return the address of cell C2

I am going to embed this cell address in another formula.

=INDEX(1:1,,MAX(IF($A$1:H$1<"x",COLUMN(A:H))))

still an array formula

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"ellebelle" wrote in message
...
sorry my mistake, i actually want the value in the cell that your formula
returns not the address as previously requested.

"Bob Phillips" wrote:

=ADDRESS(1,MAX(IF($A$1:H$1<"x",COLUMN(A:H))))

which is an array formula, it should be committed with Ctrl-Shift-Enter,
not
just Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"ellebelle" wrote in message
...
Hello, Could somebody please help me with the following


I have a row of numbers and the letter x. the numbers are always
sequential
except when an x marks a non working day.

A B C D E F G H
I J K
ROW1 x 1 2 3 4 5 x x 6 7 8
ROW2

In Row 2 I would like to get the cell address of the previous cell
that
does
not have an x in Row1.

Eg. Cell I2 would return the address of cell F2
Cell D2 would return the address of cell C2

I am going to embed this cell address in another formula.

pls forget this, i have worked it out. thanks anyway.

"ellebelle" wrote:

sorry my mistake, i actually want the value in the cell that your formula
returns not the address as previously requested.

"Bob Phillips" wrote:

=ADDRESS(1,MAX(IF($A$1:H$1<"x",COLUMN(A:H))))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"ellebelle" wrote in message
...
Hello, Could somebody please help me with the following


I have a row of numbers and the letter x. the numbers are always
sequential
except when an x marks a non working day.

A B C D E F G H
I J K
ROW1 x 1 2 3 4 5 x x 6 7 8
ROW2

In Row 2 I would like to get the cell address of the previous cell that
does
not have an x in Row1.

Eg. Cell I2 would return the address of cell F2
Cell D2 would return the address of cell C2

I am going to embed this cell address in another formula.

Bit late, I responded two hours earlier.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"ellebelle" wrote in message
...
pls forget this, i have worked it out. thanks anyway.

"ellebelle" wrote:

sorry my mistake, i actually want the value in the cell that your
formula
returns not the address as previously requested.

"Bob Phillips" wrote:

=ADDRESS(1,MAX(IF($A$1:H$1<"x",COLUMN(A:H))))

which is an array formula, it should be committed with
Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"ellebelle" wrote in message
...
Hello, Could somebody please help me with the following


I have a row of numbers and the letter x. the numbers are always
sequential
except when an x marks a non working day.

A B C D E F G
H
I J K
ROW1 x 1 2 3 4 5 x x 6 7 8
ROW2

In Row 2 I would like to get the cell address of the previous cell
that
does
not have an x in Row1.

Eg. Cell I2 would return the address of cell F2
Cell D2 would return the address of cell C2

I am going to embed this cell address in another formula.