help in re-writing this formula
Posted by Cliff on January 05, 2002 2:40 PM
I am currently using the following formula:{=choose(min(if((['Labor.xls]Day 1'!$h$32:'[Labor.xls]Day 1'!$h$36=8/24),{1;2;3;4;5}))+1,"","Kerwin","Cliff","Jamie","Adam","Fonda")}. This formula works excellent for my store. But I want the formula to be not limited to this store. I wish to send the file to other stores throughout my company. The problem with the above formula is that it displays the names, I need for it to display whatever names are in cells D32,d33,d34,d35,and d36. In the formula above it checks cells H32:H36 for the time 8:00 and then displays the names. But for it to be available for other stores without me changing all the names to the people who work at each store it just needs to display the contents in cells D32:D36. Like, if the formula finds 8:00 in cell H32 then it will display whatever name is in D32, if not it will then check H33, if it finds 8:00 it will display the name in D33 and so on. If it doesn't find 8:00 in any of the cells it will leave blank. I've tried replacing the names with just d32,d33,d34,d35,d36 but I get the number 0 with that, if I enclose each cell with "" then I get D32 or the others. Can I use the same formula to reference a cells contents instead of placing the names in it?
Posted by Aladin Akyurek on January 05, 2002 3:12 PM
Cliff --
It seems you are looking in H2:H36 for a certain time (8:00) [ which either occurs once or does not occur at all ]. And the cell in which this particular occurs has a name associated with it that you want to retrieve (If H32 = 8:00, fetch the name in D32, if H33 = 8:00, fetch the name in D33, etc). If so,
select H32:H36, go the Name Box, and type TLIST;
select D32:D36 (which houses TLIST-anchored names), go the Name Box, and type NLIST.
Use the following ordinary formula to fetch the relevant name:
=IF(COUNTIF(TLIST,"8:00"+0),INDEX(NLIST,MATCH("8:00"+0,TLIST,0)),"")
Aladin
=========
Posted by Cliff on January 05, 2002 6:59 PM
Aladin--
The formula works great but can it check for multiple times ie.8:00, 10:00,11:00,2:00,3:00,4:00,and 5:00. I have three cells that will contain the formula. The first cells formula needs to check for 8:00, the second cell needs to check for the times 10:00 and 11:00, then the third cell will need to check for the times 2:00, 3:00, 4:00, and 5:00.
Posted by Aladin Akyurek on January 05, 2002 11:47 PM
Cliff --
We already have the formula for the first cell regarding 8:00
[a1]
=IF(COUNTIF(TLIST,"8:00"+0),INDEX(NLIST,MATCH("8:00"+0,TLIST,0)),"")
or, a bit in the same style as in [b] and [c] which will follow:
[a2]
=IF(COUNTIF(TLIST,8/24)=1,INDEX(NLIST,MATCH(8/24,TLIST,0)),"")
[b] for the second cell regarding one of [ 10:00,11:00 ]
=IF(SUMPRODUCT((TLIST*24={10,11})+0)=1,INDEX(NLIST,SUMPRODUCT((TLIST >= 10/24)*(TLIST <= 11/24)*(ROW(TLIST)-(MIN(ROW(TLIST))-1)))),"")
[c] for the third cell regarding one of [ 2:00,3:00,4:00,5:00 ]
=IF(SUMPRODUCT((TLIST*24={2,3,4,5})+0)=1,INDEX(NLIST,SUMPRODUCT((TLIST >= 2/24)*(TLIST <= 5/24)*(ROW(TLIST)-(MIN(ROW(TLIST))-1)))),"")
Note that TLIST cannot have 2 or more times from a given set, e.g., 10:00 and 11:00, otherwise you'll get a blank.
Aladin
====== --
