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

 

[Duane]

Hire MrExcel!

 

[Yogi Anand]

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

 

[daleyman]

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

 

[nisht]

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

 

[swaink]

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

 

[Aladin Akyurek]

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

 

[swaink]

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

 

[Aladin Akyurek]

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?

 

[swaink]

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

 

[Aladin Akyurek]

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