Formula error: lookup referenced to =NOW cell.
Hello all, im new to community and i am trying to get over one problem for many days.
i have experinced error in return allways when I apply one formula, please help if u know where I make mistake. I made file with one sheet and maybe 5 tables, content of each cell i will put in brackets:
1. Single CELL table - (=NOW) , returns: 21/5/15, 4:48 PM
2. WEEK Schedule table - which contains 7 collumns (MON-SUN formated as date so it looks like easy to expand for future, i hope u understand)+1 header column (contains fixed times from 5:00 AM, 5:30 AM, 6:00 AM, 6:30 AM and all till 23:30 PM) + 1header row, 42rows
3. TODAY schedule - contains 1 column which is refering somehow via lookup function acording to TODAY argument searching for values in WEEKschedule table under todays date column.
4. single cell table CURRENT ACTIVITY- this is my problem, it is suppose to show current activity
THe purpose is if I enter any activity into WEEKSchedule i would like to quiclky see it in one cell. Otherwise its not really effective ofr me. Or maybe i expect too much.
wHAT I HAVE ALREADY TRIED?
1. When linking NOW table in lookup and setting searching range in new TIMEVALUE TABLE which contained smtn like (=contentate TODAY , TIME h,m,s *), ofcourse the times arguments i listed in 3 column cell for h, m, seconds separately. Ah i wish u understand. My English is very bad.
2. I tried playing around cell formats. But wow thats something magical for me. Sometimes the formats work perfect like i set the tokens and i actually see them working under custom formats. But once there is a formula such as =NOW that the format is always set to automatic and i cant try to make somehow lookup working with that as well.
* this TIME table wasnt succes because it contained about 1440 rows x 4 columns for minutes from 5am till 1AM next day. it was just freezing and slowed down a lot.
if u have any suggestions, ideas or i wasnt clear enough which i understand please let me know, i will check tomorrow and i will try things again. Have a nice day everyonec.