Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 3 of 3 FirstFirst 123
Results 21 to 30 of 30

Thread: Andy's Coach Business Scheduler

  1. #21
    Board Regular swaink's Avatar
    Join Date
    Feb 2002
    Location
    51.421818,-0.977139
    Posts
    432
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  2. #22
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,038
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    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 ]

  3. #23
    Board Regular swaink's Avatar
    Join Date
    Feb 2002
    Location
    51.421818,-0.977139
    Posts
    432
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Aladin

    Thanks I'll have a play with that


    Kev

  4. #24
    Board Regular swaink's Avatar
    Join Date
    Feb 2002
    Location
    51.421818,-0.977139
    Posts
    432
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #25
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,038
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    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

  6. #26
    Board Regular swaink's Avatar
    Join Date
    Feb 2002
    Location
    51.421818,-0.977139
    Posts
    432
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


  7. #27
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,038
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    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

  8. #28
    Board Regular swaink's Avatar
    Join Date
    Feb 2002
    Location
    51.421818,-0.977139
    Posts
    432
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  9. #29
    Board Regular swaink's Avatar
    Join Date
    Feb 2002
    Location
    51.421818,-0.977139
    Posts
    432
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  10. #30
    Board Regular swaink's Avatar
    Join Date
    Feb 2002
    Location
    51.421818,-0.977139
    Posts
    432
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •