2 Urgent Questions

[BiggieD]

Hi. Im pretty new to Spreadsheets but im doing a project at school which i
need some help with. Could you please answer these 2 questions?

1.I am making a spreadsheet for some coursework and the project is for
tickets sales. I have different sheets for different events like "Dizzee
Rascal" as one event and "ColdCut" as another event with a page which is sort
of like a home page just showing an overview of the events so far so it shows
how many tickets the event "Dizzee Rascal" has sold so far etc.

So im wanting for example from the page named "Dizzee Rascal" id like cell
E12 to also show as exactly the same even when changed on the home page.
Sorry if im not explaining this very well.

2. Also am i able to make a formula which makes the page lock after
something like "12hours and 10mins" and maybe make the formula say "EVENT
CLOSED" near the bottom of the page if possible.


Thanks in advance.

[Rayo K]

Sorry, I don't understand question #1 (maybe a little more detail will help),
but here is an idea for #2.

It depends on when you want the page to "lock" and what you mean by that. If
you just want the sheet to display "EVENT CLOSED", it's relatively simple.
Make a cell that references the start time, example "4/19/06 12:00 PM" in A1.
Have another cell b1 with a formula that is something like =NOW()-A1.
Whenever EXCEL calculates, the time will update. Now make another cell that
has something like =If(b1>12:00,"EVENT CLOSED", ""). ALl you have to do is
trigger EXCEL to calculate which you can do if the user has to update a cell
or you can write code that calculates when the sheet is activated.

If you want to make the page uneditable, you will need to lock all the
cells, then write code that protects the sheet under the same conditions as
above, except in VBA instead formula.

So in order to help you more, we just need to know exactly what you want to
happen.

HTH

All you have to do is trigger

"BiggieD" wrote:

> Hi. Im pretty new to Spreadsheets but im doing a project at school which i
> need some help with. Could you please answer these 2 questions?
>
> 1.I am making a spreadsheet for some coursework and the project is for
> tickets sales. I have different sheets for different events like "Dizzee
> Rascal" as one event and "ColdCut" as another event with a page which is sort
> of like a home page just showing an overview of the events so far so it shows
> how many tickets the event "Dizzee Rascal" has sold so far etc.
>
> So im wanting for example from the page named "Dizzee Rascal" id like cell
> E12 to also show as exactly the same even when changed on the home page.
> Sorry if im not explaining this very well.
>
> 2. Also am i able to make a formula which makes the page lock after
> something like "12hours and 10mins" and maybe make the formula say "EVENT
> CLOSED" near the bottom of the page if possible.
>
>
> Thanks in advance.

[Ikaabod]

Answer 1:
If by home page are you referring to a worksheet, then you can simply use the following formula in a cell on your worksheet "Dizzee Rascal":
=Sheet2!$D$12

(where Sheet2 is whatever you have named that worksheet... perhaps Home Page?)

Answer 2:
look at the following link for a Timer Macro.
http://www.angelfire.com/biz7/julian...ans_macros.htm

from there it's just a matter of recording a macro that protects the worksheet, changes a cell value to "EVENT CLOSED", and sets it to run at that time. Or you could have it insert a message box instead of changing a cell value:
msgbox ("EVENT CLOSED")

Hope that helps.

-Ikaabod


>Hi. Im pretty new to Spreadsheets but im doing a project at >school which i
>need some help with. Could you please answer these 2 questions?
>
>1.I am making a spreadsheet for some coursework and the project is for
>tickets sales. I have different sheets for different events like "Dizzee
>Rascal" as one event and "ColdCut" as another event with a page which is >sort
>of like a home page just showing an overview of the events so far so it >shows
>how many tickets the event "Dizzee Rascal" has sold so far etc.
>
>So im wanting for example from the page named "Dizzee Rascal" id like cell
>E12 to also show as exactly the same even when changed on the home >page.
>Sorry if im not explaining this very well.
>
>2. Also am i able to make a formula which makes the page lock after
>something like "12hours and 10mins" and maybe make the formula say >"EVENT
>CLOSED" near the bottom of the page if possible.
>
>
>Thanks in advance.

[Lonnie M.]

Hi, I'm not sure exactly what you are trying to accomplish, but I'll
take a stab at it.

1) To display the number of tickets sold on a different sheet you might
take a look at the 'COUNT', 'COUNTA', and 'SUM' functions in the help
menu. Which one you use will depend on how you setup your data.

2) For the Event Closed/Open question: For example, in cell A1 type
this formula,
'=now()', and then copy it and paste values. Change the time and date
to meet your needs and in cell B1 type this formula,
'=IF(NOW()>=A1, "Close", "Open")'. You can change this as necessary,
but it will accomplish what I believe you are trying to do.

HTH--Lonnie M.

[BiggieD]

Thats brilliant! Thankyou very much for your reply. Has really helped alot.

[BiggieD]

Hi, thanks for the reply.

1.Ive now figured this question out myself thanks.

2.I have done what you said and i have entered the "=NOW()" formula (this
has been entered at "C1" of each sheet" and it works fine. I am not going to
bother with the EVENT CLOSED sign no more though.

But what i need to know is how to make the a sheet lock on a certain date at
a certain time. My spreadsheet is about different events. Each event ends at
a different time and date. When the event ends, no more tickets should be
entered into the sheet, so, i need the sheets to lock once it reaches the
time and date that the event ends. Id like all cells except "E11" and "E15"
to lock.

Hope ive explained better this time. Thanks again.

"Rayo K" wrote:

> Sorry, I don't understand question #1 (maybe a little more detail will help),
> but here is an idea for #2.
>
> It depends on when you want the page to "lock" and what you mean by that. If
> you just want the sheet to display "EVENT CLOSED", it's relatively simple.
> Make a cell that references the start time, example "4/19/06 12:00 PM" in A1.
> Have another cell b1 with a formula that is something like =NOW()-A1.
> Whenever EXCEL calculates, the time will update. Now make another cell that
> has something like =If(b1>12:00,"EVENT CLOSED", ""). ALl you have to do is
> trigger EXCEL to calculate which you can do if the user has to update a cell
> or you can write code that calculates when the sheet is activated.
>
> If you want to make the page uneditable, you will need to lock all the
> cells, then write code that protects the sheet under the same conditions as
> above, except in VBA instead formula.
>
> So in order to help you more, we just need to know exactly what you want to
> happen.
>
> HTH
>
> All you have to do is trigger
>
> "BiggieD" wrote:
>
> > Hi. Im pretty new to Spreadsheets but im doing a project at school which i
> > need some help with. Could you please answer these 2 questions?
> >
> > 1.I am making a spreadsheet for some coursework and the project is for
> > tickets sales. I have different sheets for different events like "Dizzee
> > Rascal" as one event and "ColdCut" as another event with a page which is sort
> > of like a home page just showing an overview of the events so far so it shows
> > how many tickets the event "Dizzee Rascal" has sold so far etc.
> >
> > So im wanting for example from the page named "Dizzee Rascal" id like cell
> > E12 to also show as exactly the same even when changed on the home page.
> > Sorry if im not explaining this very well.
> >
> > 2. Also am i able to make a formula which makes the page lock after
> > something like "12hours and 10mins" and maybe make the formula say "EVENT
> > CLOSED" near the bottom of the page if possible.
> >
> >
> > Thanks in advance.