Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to change the default in Excel from "find next" to "find all" | Excel Discussion (Misc queries) | |||
SUMPRODUCT(--ISNUMBER(FIND("AM",C5:160))*(k5:k160="") | Excel Discussion (Misc queries) | |||
How to replace "#N/A" w "0"when vlookup couldn't find the match? | Excel Discussion (Misc queries) | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel |