Need a "Find Next" Formula
 

I am creating a schedule spreadsheet with sheets for everyday of the week and
the shifts. On the sheet listed €œDay€� I have Names in column A, Sections in
column B (ex would be W, E, HLS). On the sheet €œMonday€� in column A is a list
of sections (ex is W,W and E,E). I need to have the various names listed by
the assigned areas.

Before After Forumla
Sheet Day Sheet Monday Sheet Monday
Col A Col B Col A Col B Col A Col B
Jon W W W Jon
Pam E W W Tom
Tom W E E Pam
Nick E E E Nick

I could use the following formula in Monday B1 but it would return the same
name in B2.
=IF(ISNA(MATCH(A1,Day!$B$1:$B$4,FALSE)),"",INDEX(D ay!$A$1:$A$4,MATCH(A1,Day!$B$1:$B$4,0)))
Can I nest a Row formula +1?

Need a "Find Next" Formula
 

One simple set up to get you there w/o fuss
Data is assumed in row 1 down in sheets: Day & Monday

In Monday,
You have the lookup values listed in A1 down as posted
In B1: =IF(A1="","",A1&COUNTIF($A$1:A1,A1))
In C1: =IF(Day!B1="","",Day!B1&COUNTIF(Day!$B$1:B1,Day!B1 ))
In D1: =IF(A1="","",INDEX(Day!A:A,MATCH(B1,C:C,0)))
Select B1:C1, copy down to D4. Minimize/hide away cols B & C. Col D will
return the required results.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Matt" wrote:
I am creating a schedule spreadsheet with sheets for everyday of the week and
the shifts. On the sheet listed €œDay€� I have Names in column A, Sections in
column B (ex would be W, E, HLS). On the sheet €œMonday€� in column A is a list
of sections (ex is W,W and E,E). I need to have the various names listed by
the assigned areas.

Before After Forumla
Sheet Day Sheet Monday Sheet Monday
Col A Col B Col A Col B Col A Col B
Jon W W W Jon
Pam E W W Tom
Tom W E E Pam
Nick E E E Nick

I could use the following formula in Monday B1 but it would return the same
name in B2.
=IF(ISNA(MATCH(A1,Day!$B$1:$B$4,FALSE)),"",INDEX(D ay!$A$1:$A$4,MATCH(A1,Day!$B$1:$B$4,0)))
Can I nest a Row formula +1?

Need a "Find Next" Formula
 

hi, Matt !

other option is an [CSE] array-entered formula in monday sheet [B1] and copy-down:
=index(day!a:a,small(if(day!$b$1:$b$4=a1,row(day!$ b$1:$b$4)),countif($a$1:a1,a1)))

hth,
hector.

I am creating a schedule spreadsheet with sheets for everyday of the week and the shifts.
On the sheet listed "Day" I have Names in column A, Sections in column B (ex would be W, E, HLS).
On the sheet "Monday" in column A is a list of sections (ex is W,W and E,E).
I need to have the various names listed by the assigned areas.

Before After Forumla
Sheet Day Sheet Monday Sheet Monday
Col A Col B Col A Col B Col A Col B
Jon W W W Jon
Pam E W W Tom
Tom W E E Pam
Nick E E E Nick

I could use the following formula in Monday B1 but it would return the same name in B2.
=IF(ISNA(MATCH(A1,Day!$B$1:$B$4,FALSE)),"",INDEX(D ay!$A$1:$A$4,MATCH(A1,Day!$B$1:$B$4,0)))
Can I nest a Row formula +1?

Need a "Find Next" Formula
 

Thanx for the quick response guys.
Maxs solution returns a 0 if one of the cells in Day Col B is empty and
Hectors returns #NUM!. There will be days when an employee is off and I want
the cell on Monday's sheet to remain blank. I have tried to elevate this but
cannot, do anyone have a solutions.


"Matt" wrote:

I am creating a schedule spreadsheet with sheets for everyday of the week and
the shifts. On the sheet listed €œDay€� I have Names in column A, Sections in
column B (ex would be W, E, HLS). On the sheet €œMonday€� in column A is a list
of sections (ex is W,W and E,E). I need to have the various names listed by
the assigned areas.

Before After Forumla
Sheet Day Sheet Monday Sheet Monday
Col A Col B Col A Col B Col A Col B
Jon W W W Jon
Pam E W W Tom
Tom W E E Pam
Nick E E E Nick

I could use the following formula in Monday B1 but it would return the same
name in B2.
=IF(ISNA(MATCH(A1,Day!$B$1:$B$4,FALSE)),"",INDEX(D ay!$A$1:$A$4,MATCH(A1,Day!$B$1:$B$4,0)))
Can I nest a Row formula +1?

Need a "Find Next" Formula
 

In Monday,
In D1: =IF(A1="","",INDEX(Day!A:A,MATCH(B1,C:C,0)))

Try this alternative instead in D1, copied down:
=IF(A1="","",IF(ISNA(INDEX(Day!A:A,MATCH(B1,C:C,0) )),"",INDEX(Day!A:A,MATCH(B1,C:C,0))))

Above seems to drive out the results that you seek
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Matt" wrote in message
...
Thanx for the quick response guys.
Maxs solution returns a 0 if one of the cells in Day Col B is empty and
Hectors returns #NUM!. There will be days when an employee is off and I want
the cell on Monday's sheet to remain blank. I have tried to elevate this but
cannot, do anyone have a solutions.