Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 30

Thread: Andy's Coach Business Scheduler

  1. #11
    Board Regular
    Join Date
    Mar 2002
    Location
    Fort McMurray, AB
    Posts
    218
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hire MrExcel!
    Regards, Duane
    Office2010 in Win7

  2. #12
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Peter:
    You have got a lot of buddies on this board!
    You may also want to look at setting up DATA|FILTER|AUTO_Filter or Advanced Filter
    HTH
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  3. #13
    Board Regular
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    sounds like a vlookup problem to me, start with a blank worksheet and play with vlookup in simple case-scenarios until you're confident you know how it works (i always think it is easier to name a range, then refer to the name rather than always to refer to cell locations)....then try to implement something.

    your first posting makes it sound like you have a colossal task but i am sure that this is not so, tackle it in small pieces and target specific questions to this board for further help.... good luck (it will all appear obvious once you've worked it out).

    <table style="background-color:#0e54be" cellspacing="1" cellpadding="2"><td style="background-color:#ceffff;font-family:arial;color:#072c63;font-size:8pt;"> DALEY :P </td></table>

  4. #14
    Board Regular
    Join Date
    Feb 2002
    Location
    Ahmedabad Gujarat
    Posts
    303
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Well,
    I suppose you just want to see that wether a coach is booked or not in a form where you book the seat.

    Your problem can be solved by making the use of index - match function or by making use of offset function.

    Now it depends.. on you which function you want to use.

    you can download the file nos 24
    http://www.pexcel.com/download.htm

    and yes i you can also download a programme file from my same site .. file no 9 here i have show how you can use index match function to get the "marksheet printed."

    ni****h desai
    http://www.pexcel.com

    [ This Message was edited by: nisht on 2002-04-13 23:25 ]

  5. #15
    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 All

    Just to let you know I've given Peter this Formula which is embedded in a macro. It seems to do the trick.

    It also gives the warning "Check the data range" if it doesn't find the date
    range.

    =IF(ISNA(INDEX(DataRange,MATCH(($F$6),Data!$B$1:$B$366,),MATCH($D$5,Data!$B$1:$H$1,)=TRUE)),"Check the Data Range ",INDEX(DataRange,MATCH(($F$6),Data!$B$1:$B$366,),MATCH($D$5,Data!$B$1:$H$1,)))

    Where D5 is the registration number and F6 is the outbound data.

    The macro also returns each days reference for the days specified. Ie Outbound 1/4/02 Inbound 5/4/02.

    Kev

    [ This Message was edited by: swaink on 2002-04-14 05:17 ]

  6. #16
    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:07, swaink wrote:
    Hi All

    Just to let you know I've given Peter this Formula which is embedded in a macro. It seems to do the trick.

    It also gives the warning "Check the data range" if it doesn't find the date
    range.

    =IF(ISNA(INDEX(DataRange,MATCH(($F$6),Data!$B$1:$B$366,),MATCH($D$5,Data!$B$1:$H$1,)=TRUE)),"Check the Data Range ",INDEX(DataRange,MATCH(($F$6),Data!$B$1:$B$366,),MATCH($D$5,Data!$B$1:$H$1,)))

    Kev
    Kev,

    What houses B1?

    Aladin

  7. #17
    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 listed all the dates for a year down the sheet and the registration numbers as headers to each column. I generated a unique reference using the first 4 chars of the registration then the date. this is all on a seperate sheet called Data. and the range is B1:B366


    Kev

  8. #18
    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:22, swaink wrote:
    Hi Aladin

    I listed all the dates for a year down the sheet and the registration numbers as headers to each column. I generated a unique reference using the first 4 chars of the registration then the date. this is all on a seperate sheet called Data. and the range is B1:B366


    Kev
    Fine. But what did you put in B1? My guess is that it's empty or contains a label.

    Right?

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

    Yeh

    B1 contains "Registration" in B2 I've put "Capacity" with the number of seats for each bus just as a bit of extra info for peter.

    Then the dates start in B3

    Kev

    [ This Message was edited by: swaink on 2002-04-14 05:32 ]

  10. #20
    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:30, swaink wrote:
    Yeh

    B1 contains "Registration" in B2 I've put "Capacity" with the number of seats for each bus just as a bit of extra info for peter.

    Then the dates start in B3

    Kev

    [ This Message was edited by: swaink on 2002-04-14 05:32 ]
    I think the formula that you use should be OK, although it can be shortened a bit. It's also possible to use a different type of formula. If interested, post 10 rows of data including the labels down and across from the layout that you created. The best way to do is that you select an empty cell, type = in thet cell, select the first 10 rows including labels, hit F9, copy what you see behind the =-sign, and paste it in the follow up.

    Aladin

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
  •