Link Cell From 1 Sheet to Another: Pull Data Based On Time

    Hello everyone,


    I have a BIG problem. I'm trying to make a floor plan which shows what seats are available depending on the time it is. So I made sheet1 = to the map (where the seats are located), sheet2 - rows = seat #'s, columns = Time (broken down by half hours). Now i have conditional format to show "t" in red (which means taken), "f" in blue (which means free), and "n" to show in gray (which means not working).


    What i need is example - Seat # 200 in sheet 1 to show red, blue, or gray depending on the value in the row that corespond to this seat and the column that corespond to the current time.


    Please HELP I have a couple of formula's but nothing is working.


    Attached I added the sheet for reference.

    Re: Link A Cell From Sheet1 To Sheet2 To Pull Data Depending On The Time


    don't think this is too hard. First thing, how often does this need to check the time? I think the "on-time" method is the best way to go here. This will provide the ability for the worksheet to execute at certain times to check the options set...ie what you want.


    Let me see what I can do.

    Re: Link A Cell From Sheet1 To Sheet2 To Pull Data Depending On The Time


    the times you show on the sheet are in half hour increments. Explain how the time blocks are allocated though....


    If i enter an "f" in the 8:30 grid...does this mean free from 8:00 to 8:30 or 8:30 to 9:00 ?

    Re: Link A Cell From Sheet1 To Sheet2 To Pull Data Depending On The Time


    Quote from gmccreedy

    the times you show on the sheet are in half hour increments. Explain how the time blocks are allocated though....


    If i enter an "f" in the 8:30 grid...does this mean free from 8:00 to 8:30 or 8:30 to 9:00 ?


    Well the way it would work is each cell should have a value ("t", "f", or "n") trough out the day. so if there is no value on the on the cell for 8 to 8:30 is should be blank. Unless if is easier to just have it look as free if there is no value on the cell.

    Re: Link A Cell From Sheet1 To Sheet2 To Pull Data Depending On The Time


    Here is my first go at it.


    It took some "twekaing" to make it work since the time entry you specify is not really time in excel...its a number. I was able to convert it to time for the time check by putting a hiden row in (row 3).


    I am still not sure if this is correct as far as what you want, but let me know. I would suggest you test this thoroughly before you make this a standard template...make sure all those conditional formats are really calling on the right information.


    Any problems, get back to me.

    Re: Link A Cell From Sheet1 To Sheet2 To Pull Data Depending On The Time


    gmccreedy that was great work... I'm just testing it out and waiting for the time to change but so far that is exactly what I needed. I do have a few questions for you.


    Where are the formulas? How would I be able to change the values from "f" to "a", and "n" to "u"?


    Also one more thing I wanted to ask... I know how to add the todays date but how can I add the date and time it was last save...


    Ok Ok one more thing... is there anyway to add a clock on the sheet1 to show the current time?

    Re: Link A Cell From Sheet1 To Sheet2 To Pull Data Depending On The Time


    Quote from emelendez80


    Where are the formulas? How would I be able to change the values from "f" to "a", and "n" to "u"?


    All the formulas are in the condtional format for each seat.


    Quote from emelendez80


    Also one more thing I wanted to ask... I know how to add the todays date but how can I add the date and time it was last save...


    Not sure what you want here...you want a log on the sheet of when it was saved??? keep in mind that as time progresses the sheet will change accordingly...upon hitting enter or doing an action on the screen. The sheet will not "automatically" change. It will only change when you do something on the sheet that causes the "now" equation to update.


    Quote from emelendez80


    Ok Ok one more thing... is there anyway to add a clock on the sheet1 to show the current time?


    You want a running clock...do a search on this site. There are tons of examples. I will warn you though that this will, in my opinion, cause undue lagging in the sheet.


    However...you could just add a formula to a cell

    Code
    =now()


    and this will give you an instant date and time. Each time you hit "enter", the time will be readjusted.

    Re: Link A Cell From Sheet1 To Sheet2 To Pull Data Depending On The Time


    Dave, I helped him out with no macros...just conditional formatting and formulas. But that is definetly a great option here.


    Like i mentioned earlier...the way he was entering his time values was not really an excel format of time.


    emelendez80. BTW. The way it's set up, you shouldn't change any of the time slots on the entry sheet. I had to add a 12 hour increment after noon past since you were only utilizing a 12 hour cycle, but excel doesn't recognize time without the AM/PM designation. So...in order to keep your sheet consistent with the way you set it up, I made it work. (See the hidden row 3 in worksheet.)

    Re: Link Cell From 1 Sheet to Another: Pull Data Based On Time


    Hello again,


    Here is a crazy questions. If I add a calender would I be able to have the links update depending on the date?


    For example... I have 31 sheets which are all name with the date (10.17.06, 10.18.06) I want to make a calender so i can click On the date I want to see and it will take me to the sheet that has that date.

    Re: Link Cell From 1 Sheet to Another: Pull Data Based On Time


    Ok here is the sheet has I have it now. I used a calender I found here (Thanks to the person who made it). I need to click on the date and have it bring me to the correct sheet. The problem I have is that the dates could change so I need to have it work no matter what date is in the cell. Please help.


    Let me know if that even made any sense.


    thanks in adavance.


    Well the sheet is too big and I'm not able to uploaded... Let me know if what if you have any questions.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login