Andy's Coach Business Scheduler

[swaink]

Hi Aladin

That works a treat, may I ask a question of you before I post it.

I've now used CELL with the same formula to return the address of the field found by the formula.

The question? Is it possible to set the cell found to read "Booked" if the bookin is confirmed.

Kev

 

[swaink]

HI Aladin

I hope I did this right

"Registration","K137 NWV","RF51 WHR","RJ02 VFG","RY51 XAN","S510 SLD","W176 YRT"
"Capacity","30 seater","62 Seater","15 Seater","62 Seater","62 Seater","15 Seater";
01/01/02,"K137 37257","RF51 37257","RJ02 37257","RY51 37257","S510 37257","W176 37257";
02/01/02,"K137 37258","RF51 37258","RJ02 37258","RY51 37258","S510 37258","W176 37258";
03/01/02,"K137 37259","RF51 37259","RJ02 37259","RY51 37259","S510 37259","W176 37259";
04/01/02,"K137 37260","RF51 37260","RJ02 37260","RY51 37260","S510 37260","W176 37260";
05/01/02,"K137 37261","RF51 37261","RJ02 37261","RY51 37261","S510 37261","W176 37261";
06/01/02,"K137 37262","Booked","RJ02 37262","RY51 37262","S510 37262","W176 37262";
07/01/02,"K137 37263","RF51 37263","RJ02 37263","RY51 37263","S510 37263","W176 37263"}

Kev
This message was edited by swaink on 2002-04-14 06:18

 

[Aladin Akyurek]

On 2002-04-14 05:51, swaink wrote:
HI Aladin

I hope I did this right

{"Registration","K137 NWV","RF51 WHR","RJ02 VFG","RY51 XAN","S510 SLD","W176 YRT";"Capacity","30 seater","62 Seater","15 Seater","62 Seater","62 Seater","15 Seater";37257,"K137 37257","RF51 37257","RJ02 37257","RY51 37257","S510 37257","W176 37257";37258,"K137 37258","RF51 37258","RJ02 37258","RY51 37258","S510 37258","W176 37258";37259,"K137 37259","RF51 37259","RJ02 37259","RY51 37259","S510 37259","W176 37259";37260,"K137 37260","RF51 37260","RJ02 37260","RY51 37260","S510 37260","W176 37260";37261,"K137 37261","RF51 37261","RJ02 37261","RY51 37261","S510 37261","W176 37261";37262,"K137 37262","Booked","RJ02 37262","RY51 37262","S510 37262","W176 37262";37263,"K137 37263","RF51 37263","RJ02 37263","RY51 37263","S510 37263","W176 37263"}

The dates are appearing here as a numeric rather than the date format.

Kev
This message was edited by swaink on 2002-04-14 05:54

Kev,

You did it perfect. This proc enables to enter the sample data in one go, without any typing and any mistakes.

Here is an alternative approach. First exchange the rows that start with Registration and Capacity. Not that it is strictly necessary to do so, it appears more appealing and manageable. Moreover, this moves separates data from its description.

=IF(AND(COUNTIF(Data!$B$2:$B$366,F6),COUNTIF(Data!$C$1:$H$1,D5)),OFFSET($B$1,MATCH(F6,Data!$B$2:$B$366,0),MATCH(D5,Data!$C$1:$H$1,0)),"Check the Data Range")

If you name DATES the range Data!$B$2:$B$366 via the Name Box on the Formula Bar and REGNUMS the range Data!$C$1:$H$1, you can use instead:

=IF(AND(COUNTIF(DATES,F6),COUNTIF(REGNUMS,D5)),OFFSET($B$1,MATCH(F6,DATES,0),MATCH(D5,REGNUMS,0)),"Check the Data Range")

Aladin
This message was edited by Aladin Akyurek on 2002-04-15 05:34

 

[swaink]

Aladin

Thanks I'll have a play with that


Kev

 

[swaink]

Hi Aladin

I am able to see my post but I also had to search when I came to see if you had replied


Kev

 

[Aladin Akyurek]

On 2002-01-14 13:14, swaink wrote:
Hi Aladin

That works a treat, may I ask a question of you before I post it.

I've now used CELL with the same formula to return the address of the field found by the formula.

The question? Is it possible to set the cell found to read "Booked" if the bookin is confirmed.

Kev

Hi Kev,

Now we can look at the question: How do we know something is "Booked"?

Aladin

 

[swaink]

Hi Aladin

The macro I wrote fills C12 down with the date range and E12 down with the references.

I'm looking to use the CELL and your formula in F12 down to indicate the address of the references found.

I've used a drop down to enable peter to select a coach which when selected activates the macro refreshing the cells with the date range and references.

I propose to put a button on the form for him to confirm the booking, a macro would then change the corresponding address's found with the word Booked.

Kev

 

[Aladin Akyurek]

On 2002-04-15 05:09, swaink wrote:
Hi Aladin

The macro I wrote fills C12 down with the date range and E12 down with the references.

I'm looking to use the CELL and your formula in F12 down to indicate the address of the references found.

I've used a drop down to enable peter to select a coach which when selected activates the macro refreshing the cells with the date range and references.

I propose to put a button on the form for him to confirm the booking, a macro would then change the corresponding address's found with the word Booked.

Kev

Kev,

Seen your WB. I'm impressed. Hard to give a summary here what you're up to. I'd suggest using

="Data!"&ADDRESS(MATCH(C12,DATES,0)+2,2)

instead of CELL embedding the whole formula.

Aladin

 

[swaink]

Hi Aladin

Thanks for that my only problem now is over writing the address found with "Booked"

I think I'll post this as a seperate question to see what comes back

Kev

 

[swaink]

Hi Peter

With the help of Aladin and Mark Ive sent you a spread sheet that doe's as you wished with a little bit extra rolled in.

Thanks to Aladin and Mark for your support

Best regards

Kev
This message was edited by swaink on 2002-04-21 09:21

 

[swaink]

On 2002-04-21 09:20, swaink wrote:
Hi Peter

With the help of Aladin and Mark Ive sent you a spread sheet that doe's as you wished with a little bit extra rolled in.

Thanks to Aladin and Mark for your support

Best regards

Kev
This message was edited by swaink on 2002-04-21 09:23