Find first and last cells in range, return info beside
 

Hi All,

I hope someone can help with a tricky formula. Given this type of
sample data:

A B C D E
F G
1 Jan-10 Feb-10 Mar-10 Apr-10 May-10 Jun-10 Jul-10
2 x
x x
3 x x x x
4 x x
5
x x x
6
7

Looking at row 2 -
In cell A6, find the first (left most) x and return the month-year in
the row above it. This should give Apr-10.
In cell A7, find the last (right most) x and return the month-year in
the row above it. This should give Jun-10.
Then I should be able to determine the formula for rows 3 to 5.

If someone knows this, it will save a lot of people manual entries.

Thanks!

Michele

Find first and last cells in range, return info beside
 

Try these...

For the leftmost "x":

=INDEX(A1:G1,MATCH("x",A2:G2,0))

For the rightmost "x":

=INDEX(A1:G1,MATCH("xxxxx",A2:G2))

--
Biff
Microsoft Excel MVP


"mjones" wrote in message
...
Hi All,

I hope someone can help with a tricky formula. Given this type of
sample data:

A B C D E
F G
1 Jan-10 Feb-10 Mar-10 Apr-10 May-10 Jun-10 Jul-10
2 x
x x
3 x x x x
4 x x
5
x x x
6
7

Looking at row 2 -
In cell A6, find the first (left most) x and return the month-year in
the row above it. This should give Apr-10.
In cell A7, find the last (right most) x and return the month-year in
the row above it. This should give Jun-10.
Then I should be able to determine the formula for rows 3 to 5.

If someone knows this, it will save a lot of people manual entries.

Thanks!

Michele

Find first and last cells in range, return info beside
 

On May 14, 9:06*pm, "T. Valko" wrote:
Try these...

For the leftmost "x":

=INDEX(A1:G1,MATCH("x",A2:G2,0))

For the rightmost "x":

=INDEX(A1:G1,MATCH("xxxxx",A2:G2))

--
Biff
Microsoft Excel MVP

"mjones" wrote in message

...

Hi All,

I hope someone can help with a tricky formula. *Given this type of
sample data:

* * * *A * * * * * *B * * * * * *C * * * * * D * * * * * E
F * * * * * G
1 *Jan-10 * *Feb-10 * *Mar-10 * *Apr-10 * *May-10 * *Jun-10 * *Jul-10
2 * * * * * * * * * * * * * * * * * * * * * * * *x
x * * * * * *x
3 * * * * * * * * * *x * * * * * * x * * * * * *x * * * * * x
4 * * *x * * * * * *x
5
x * * * * * *x * * * * * x
6
7

Looking at row 2 -
In cell A6, find the first (left most) x and return the month-year in
the row above it. *This should give Apr-10.
In cell A7, find the last (right most) x and return the month-year in
the row above it. *This should give Jun-10.
Then I should be able to determine the formula for rows 3 to 5.

If someone knows this, it will save a lot of people manual entries.

Thanks!

Michele

Thank you very much. Your formulas work great on a test worksheet.
Now I just have to determine why they are giving me a #NA - value not
available to the formula or function. There are month/years in all
the appropriate rows. I'm sure I'll work it out. Thanks again.

Find first and last cells in range, return info beside
 

There are month/years in all the appropriate rows

It looks like x's in the data rows and the date (to be returned) is the in
the header row.

Both formulas are specifically looking for x's in the data rows.

--
Biff
Microsoft Excel MVP


"mjones" wrote in message
...
On May 14, 9:06 pm, "T. Valko" wrote:
Try these...

For the leftmost "x":

=INDEX(A1:G1,MATCH("x",A2:G2,0))

For the rightmost "x":

=INDEX(A1:G1,MATCH("xxxxx",A2:G2))

--
Biff
Microsoft Excel MVP

"mjones" wrote in message

...

Hi All,

I hope someone can help with a tricky formula. Given this type of
sample data:

A B C D E
F G
1 Jan-10 Feb-10 Mar-10 Apr-10 May-10 Jun-10 Jul-10
2 x
x x
3 x x x x
4 x x
5
x x x
6
7

Looking at row 2 -
In cell A6, find the first (left most) x and return the month-year in
the row above it. This should give Apr-10.
In cell A7, find the last (right most) x and return the month-year in
the row above it. This should give Jun-10.
Then I should be able to determine the formula for rows 3 to 5.

If someone knows this, it will save a lot of people manual entries.

Thanks!

Michele

Thank you very much. Your formulas work great on a test worksheet.
Now I just have to determine why they are giving me a #NA - value not
available to the formula or function. There are month/years in all
the appropriate rows. I'm sure I'll work it out. Thanks again.