Train Ticket based on Dabba No

**majidsiddique** · 01-29-2019, 06:06 AM

Hi all,
kindly help me, i am working in this sheet, there are three parts,
1 - allocation of train ticket
2- return
3- exchange of ticket
first i have to restrict the cell L4 sheet T-B-Economy, if exceed 20 then message will come "No Seat" same for next M4 "No Berth".
actually 1 Dabba have 20 seats and 40 berth economy and AC class. sheet T-A will give you complete pic of Train detailed. second is number allotment is too difficult like,
ticket issue to customer X - 1 to 10
next series will be generate for same Dabba 11 to 20 for seats
if Train 1 and Dabba 1 is full by seat and berth then goto second dabba.
you can see the sequence of sheet T-B-Economy and T-B-AC.
in T-C sheet i use formula to find booked and remaining seat and berth, so there is a problem of range selection of Dabba.
Thanks.

**JeteMc** · 02-03-2019, 10:52 AM

first i have to restrict the cell L4 sheet T-B-Economy, if exceed 20 then message will come "No Seat" same for next M4 "No Berth".

I am not sure that I understand all of what is being requested so I will attempt to address this one point first.
The following custom data validation formula is applied to L4:L22 =SUMIFS(L$4:L4,G$4:G4,G4,E$4:E4,E4)<=20
Similarly for M4:M22 =SUMIFS(M$4:M4,G$4:G4,G4,E$4:E4,E4)<=40
Let us know if you have any questions.

**Pepe Le Mokko** · 02-03-2019, 11:42 AM

welcome to the forum

For future reference, please take a moment to read the forum rules and use thread titles that are descriptive of your problem . (think browser search terms?).

Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

**majidsiddique** · 02-04-2019, 02:32 AM

Hi Pepe Le Mokko,
is there need to change Title Thread?

**AliGW** · 02-04-2019, 02:35 AM

Yes, the title needs changing, please.

**majidsiddique** · 02-04-2019, 02:38 AM

Thanks JeteMC.
one issue is solved, can any help to exchange and return?

**AliGW** · 02-04-2019, 03:19 AM

You need to change the thread title first, please.

**majidsiddique** · 02-04-2019, 04:42 AM

Hi AliGW,
can you please tell me step of changing the thread?

**AliGW** · 02-04-2019, 05:02 AM

To change a title go to your first post, click EDIT then Go Advanced and change your title.

**JeteMc** · 02-05-2019, 10:42 AM

Thank You for changing the title.
If I understand correctly you could use the following formula to fill in the 'Return' information based on ticket number:

Formula:

Please Login or Register  to view this content.

Note that in row 6 the formula needs to end with ...ROW(4:4) instead of ...ROW(3:3) because the Client Name is not being displayed on this form.
The use of this formula is modeled on the 'RETURN' sheet (font red)
Looking at the 'Exchange...' sheets the same formula should work for them also.
Let us know if you have any questions.

**majidsiddique** · 02-06-2019, 02:06 AM

Hi JeteMC,
great, OK first we focus on return the same scenario. i use your formula its working perfect. there are two possibilities in ticket return, first if customer said fully ticket return so its mean Train 1 and Dabba No 5 Economy 10 Seat and 20 Berth fully cancel and available in same date. another case is the customer said that 5 Seat and 10 Berth will cancel on same Ticket so it mean 5 Seat and 10 berth booked in the same Ticket No and rest of 5 Seat and 10 Berth will be available to other customer.
when customer fully cancel the ticket then please mention T-B-Economy sheet Ticket No E-1000007 next column shows "Ticket Return" and Today Date.
Exchange will be discussed later.

**JeteMc** · 02-08-2019, 09:40 PM

Again, if I understand correctly, then you could use the following formula to populate cell E5 on the T-C sheet:

Formula:

Please Login or Register  to view this content.

The following could then populate cell F5:

Formula:

Please Login or Register  to view this content.

Similar formulas could be used to populate G5:H5.
If the customer only cancels some of the seats/berths then those numbers would have to manually be placed in the cells in rows 14:15 on the Return Sheet.
Let us know if you have any questions.

**majidsiddique** · 02-09-2019, 01:43 AM

Hi JeteMC,
great work, kindly add one thing more in sheet T-B-Economy when ticket return fully or partially so on this sheet can be highlighted ROW and in front remarks i mention on it.

**JeteMc** · 02-09-2019, 12:57 PM

This proposal employs two helper columns (V:W),which may be moved and/or hidden for aesthetic purposes, to the T-B-Economy sheet.
Columns V:W are populated using: =IF(S4="","",INDEX(RETURN!C$14:J$14,MATCH(R4,RETURN!C$2:J$2,0)))
Column S is populated using: =IF(ISNUMBER(MATCH(R4,RETURN!C$2:J$2,0)),"Ticket Cancel","")
Column T is populated using: =IF(S4="","",IF(AND(L4=V4,M4=W4),"Fully",""))
Column U is populated using: =IF(AND(S4="Ticket Cancel",T4=""),"Partial "&V4&" Seat and "&W4&" Berth","")
Conditional formatting uses: =$S4="Ticket Cancel"
Let us know if you have any questions.

**majidsiddique** · 02-11-2019, 01:53 AM

Hi JeteMC,
its wirking great, But tell me one thing more, i have to share in ticket in allocation based on train and Dabba No. For Example i use Seat and Berth allocate to customer 10 Seats and 10 Berth, But did not mention where to start this no. one Dabba seat 20 and berth 40 also mention first sheet. can you tell me how to settle this issue. i issue 10 seat to mention but where to start No 1 to 10 or 5 to 15 or 10 to 20 i also include 2 more column in T-B-Economy Sheet, but don't use any formula to apply it because all formula disturbed by this. do you have any suggestion for this.
i also include final stage exchange in the attached sheet. exchange ticket will show on last of T-B-Economy

But first to settle allocation of seat and berth numbers.

**majidsiddique** · 02-11-2019, 01:56 AM

please review attched file

**avk** · 02-11-2019, 05:36 AM

First change "Dabba" word. Because most of person not know this word. Say as Rail Coach.
In "T-B-Economy" sheet in column "Z4" =LEFT(T4,2)
In "AA4" column :

Formula:

Please Login or Register  to view this content.

copy paste down.
in "AA1"

Formula:

Please Login or Register  to view this content.

Kindly change range as per your data record in case of row more than 22.
For max value. In "Z1" is E-
In sheet "EXCHANGE-E" in "B24"

Formula:

Please Login or Register  to view this content.

**majidsiddique** · 02-11-2019, 06:31 AM

Hi AVK,
i changed the word Dabba No but tell me how to deal with Seat and Berth numbers. when i goto T-C sheet for checking Seat and Berth available in a train. i do small change in return sheet and then goto T-C sheet for checking rail coach booking and available Seat and Berth.

**JeteMc** · 02-12-2019, 12:51 PM

Perhaps the following will be useful.
Helpers are added in columns I:L
Formulas in columns I and K are similar to: =IF(E5>0,1,0)
Formulas in columns J and L are similar to: =SUM(E$5:E5)
The formulas that display the ticket numbers are similar to: =I5&" to "&J5
Let us know if you have any questions.

**majidsiddique** · 02-13-2019, 01:04 AM

very smart, really fantastic, i add one more new to check perfect,
let go to final stage Exchange Ticket, Exchange-E sheet i made it exchange ticket E-1000003, it exchange cancel and issue new ticket, when issue new then copy to paste T-B-Economy sheet as i paste it. you can see the file and how to deal with remain old formulas. kindly restriction return and exchange sheet when to enter ticket no cheeked it before cancel or exchange over all T-B-Economy Sheet.
exchange-E sheet prior to make this ticket seat and berth was available in this train and Rail coach after pasting exchange ticket data it is full.

**JeteMc** · 02-13-2019, 09:59 PM

I saw that E11:F11 were reporting seat/berth numbers even when there were no seats/berths available. I believe that corrections have been made such that when the values in E10:F10 are zero, i.e. IF(E10=0..., then the seat/berth numbers will display N/A.
I will have to look at the Exchanges later.

**majidsiddique** · 02-14-2019, 12:19 AM

Hi JeteMc,
it is OK.

**JeteMc** · 02-16-2019, 02:59 PM

If I understand correctly then the following change to the formula for columns X:Y on the T-B-Exchange sheet should help:

Formula:

Please Login or Register  to view this content.

I modified the formula in columns B:P on the Exchange-E sheet, which required modifying a few headers on both the T-B-Exchange and Exchange-E sheets:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

**majidsiddique** · 02-18-2019, 01:26 AM

Great work boss,
thanks a lot. you are so smart.
really cheers.

**JeteMc** · 02-18-2019, 07:40 AM

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

Train Ticket based on Dabba No

Re: Train Ticket Issuance problem

Re: Train Ticket Issuance problem

Re: Train Ticket Issuance problem

Re: Train Ticket Issuance problem

Re: Train Ticket Issuance problem

Re: Train Ticket Issuance problem

Re: Train Ticket Issuance problem

Re: Train Ticket Issuance problem

Re: Train Ticket based on Dabba No

Re: Train Ticket based on Dabba No

Re: Train Ticket based on Dabba No

Re: Train Ticket based on Dabba No

Re: Train Ticket based on Dabba No

Re: Train Ticket based on Dabba No

Re: Train Ticket based on Dabba No

Re: Train Ticket based on Dabba No

Re: Train Ticket based on Dabba No

Re: Train Ticket based on Dabba No

Re: Train Ticket based on Dabba No

Re: Train Ticket based on Dabba No

Re: Train Ticket based on Dabba No

Re: Train Ticket based on Dabba No

Re: Train Ticket based on Dabba No

Re: Train Ticket based on Dabba No

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Selecting Nearest Time And Train Line From Set Train Schedule (2017)

[SOLVED] [SOLVED] automatic ID issuance in MS Exel worksheet

Bookmarks

Bookmarks

Posting Permissions