# 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.

## Files

• 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

Well the idea is to have it update every 30 minutes. So if thats possible that would be great.

• 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.

## Files

• 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

gmccreedy...

Everything worked just as needed.... It would have taken me days (new to this)... But thank you very much.

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

your very welcome. any problems, just shoot me a PM.

• 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 A Cell From Sheet1 To Sheet2 To Pull Data Depending On The Time

No problems. The link is mainly for other that find the Thread via a search.

• 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

absolutly...just have to set the date as a variable somewhere on the table chart.

Do you have a sheet set-up already with the 31 other sheets?

• 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.