Friday, December 9, 2011

yogi_Detect A Number Such As 1,2,3 And Put Associated Letter In Another Cell -- TimeSheet/WorkFlow Related

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user Crackers707 said:
Detect a 1,2,3 and put a letter in another cell - multi complex formula
I've tried searching and searching but I don't think anyone has asked this before. Essentially I am trying to make a timesheet for multiple employees. Each employee has a Tab along the bottom. The day/date is across the top left->right and the hours down the left side. Each employee will manage their times by placing a number in the respective date/time as such:
0 - unavailable
1 - available
2 - busy
The cells change colour depending on the number in them, so this feature is working fine.
What I want is an overview, so at a glance I can see when people have free time available. The setup for the overview sheet will be the same, date up top, times down the side. Now on each particular day I want to know what employees are available.... sooo in simple terms this is what i would like to achieve:
=If David's A2 = 1, then it would put a D in the cell, but at the same time, if Johns A2 = 1, then it would also put a D in the cell and so on.....
Eseentially the cell might have ADHG to indicate 4 people are available at that particular time.  I have my doubts but is this possible???
Thanks so much :)
update:
I think now i've actually made this doc public:  https://docs.google.com/spreadsheet/ccc?key=0AocB8_vmgHtndFNyd2xqOHlUdm5ackwzMUtkUUdBSWc
As i've written above I want it so that each employee has their own availability tab, marking in each timeslot whether they are available or not
The overview page then needs to give me an indication as to what people are available on each day/time. Probably indicated by the letter of their first name... I hope this makes sense... I don't think it's going to be possible so <don't> bother spending too much time stuffing around with it
Thanks, look forward to hearing a response.
-----------------------------------------------------------
following is a solution to the problem