Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
nested "If" fuction
that would be because you moved up 101 cells, so 4 becomes -97. Assuming
you'll want $F$4 , $J$4 and such. That will lock down row 4 for all those. "Jadeyze" wrote: Jacob: Thanks! This seems to work, but when I try to copy to the other cells (I get a #REF! message - see below. I can't get it to keep the cell reference F3:R3 for the truckers names. =INDEX('Mapleton Ranking'!#REF!,MATCH(1,'Mapleton Ranking'!$F12:$R12,0)) After I can get the first choice I know to change tweek the formula for 2, 3 etc. Is there an easier way to do this or do I need to go into each and every cell and manually change? "Jacob Skaria" wrote: In the same cell try the below to get the 1st choice..replace 1 with 2,3 for the rest =INDEX('Mapleton Ranking'!$G4:$M4,match(1,'Mapleton Ranking'!G113:M113,0)) If this post helps click Yes --------------- Jacob Skaria "Jadeyze" wrote: I ranked the truckers by price. No Problem, Now I want to show what trucker placed 1st, 2nd or 3rd. not much of a problem but I have more than 7 carriers so I can't use the below formula. How can I tell Excel to look across a row, determine which cell has a number (1,2,3,4,...) and based on the number in the cell type the column header(truckers name) Dest. City ST Quality Cox Barr Trans ABBEVILLE AL 1 3 2 IRVINGTON AL 3 1 #N/A So the result should look like this: Primary 2nd Choice 3rd choice ABBEVILLE AL Quality Barr Trans Cox IRVINGTON AL Cox tie Quality =IF('Mapleton Ranking'!F113=1,'Mapleton Ranking'!$F4,IF('Mapleton Ranking'!G113=1,'Mapleton Ranking'!$G4,IF('Mapleton Ranking'!H113=1,'Mapleton Ranking'!$H4,IF('Mapleton Ranking'!I113=1,'Mapleton Ranking'!$I4,IF('Mapleton Ranking'!J113=1,'Mapleton Ranking'!$J4,IF('Mapleton Ranking'!K113=1,'Mapleton Ranking'!$K4,IF('Mapleton Ranking'!L113=1,'Mapleton Ranking'!$L4,IF('Mapleton Ranking'!M113=1,'Mapleton Ranking'!$M4,"TIE")))))))) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
Nested "IF" With "And"and "OR" functions | Excel Worksheet Functions | |||
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 |