• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Customizin Excel Payment Workbook

Shabbo

Member
Dear Sir,

Ref to attached excel sheet ,it’s my payment sheet I enter data into this then I import into my software, I wanted it to customized as follows.

1) If I enter data into A column then L column should write D

2) Column J should get entry no from master sheet but against vehicle no from sheet 1 column A but it should only with last 4 digit because in some cases initial no may be different but last 4 digit will remain same.

3) Once Entry # come in column J then column E should show billing and column I should show p3

Thanks.
 

Attachments

  • Copy of August Transaction.xlsx
    15.7 KB · Views: 1
Hi:

Find the attached all orange color cells are having formulas.

Thanks
 

Attachments

  • Copy of August Transaction.xlsx
    17.1 KB · Views: 2
Dear sir,

You have done a great job which has saved a lot my time but still I want few things to do.

1) Entry no that is column J should consider date as well the same vehicle is repeated on different dates .

2) If entry column is blank then vehicle no should pick from z c olumn as example says.
 

Attachments

  • Copy of Copy of August Transaction-1.xlsx
    18.1 KB · Views: 0
Hi:

Now you have added some more data which was not present in your previous file, and the note column now contains other texts as well. With this additional information, I guess you have to clean up the note column before applying any of my formula. Once you cleanup your note column the modified formulas will work. I am not sure if there is any common texts that accompanies the registration numbers in the note column if yes a helper column can be created to remove these additional texts in the note column.

Find the attached.

Thanks
 

Attachments

  • Copy of Copy of August Transaction-1.xlsx
    19.6 KB · Views: 0
Hi:

Now you have added some more data which was not present in your previous file, and the note column now contains other texts as well. With this additional information, I guess you have to clean up the note column before applying any of my formula. Once you cleanup your note column the modified formulas will work. I am not sure if there is any common texts that accompanies the registration numbers in the note column if yes a helper column can be created to remove these additional texts in the note column.

Find the attached.

Thanks

Dear Sir,

Thanks for your help, but my query is if there is Entry Number mentioned in Column K then Vehicle number should come from Master Data sheet and if it blank then Vehicle number should come from z column...because wherever entry number is mentioned those entries are related to job and vehicle number should be against that job and wherever entry number not mentioned those entries are non job and vehicle number should on the basis on narration.

please advise.
 
Dear Sir,
It works fine, only concern is that entry number should consider date as well because vehicle number are repeated on different dates if you resolve this thing its really good payment workbook you have developed.
 
Hi:

I have taken care of it by counting the vehicle if the count is more than 1 it will populate blank. Or else please specify which column you want the formula in.

Thanks
 
Dear Sir,

Eg : If Vehicle number 2578 has used on 1ast August and again on 16th August.

Then If I put date in Column A that is 16th August then Entry should come from Master sheet for that vehicle for 16th August only.

One more egg : if vehicle number 2578 has got entry number 1196 for dated 16th August 2015, then Entry should come 1196 in sheet 1 Column K should show 1196 but Now Colum K is giving entry number 1190.
Hi:

I have taken care of it by counting the vehicle if the count is more than 1 it will populate blank. Or else please specify which column you want the formula in.

Thanks


Dear Sir,

Eg : If Vehicle number 2578 has used on 1ast August and again on 16th August.

Then If I put date in Column A that is 16th August then Entry should come from Master sheet for that vehicle for 16th August only.

One more egg : if vehicle number 2578 has got entry number 1196 for dated 16th August 2015, then Entry should come 1196 in sheet 1 Column K should show 1196 but Now Colum K is giving entry number 1190.
 
Hi:

Find the attached , the column K formula now take into account the date factor as well.

Thanks
 

Attachments

  • Copy of Copy of August Transaction-1.xlsx
    19.6 KB · Views: 0
Dear Sir,
Formula works fine, but now I am getting same sheet with two types of transaction which needs to fit in one formula as below.

1) Column K should pick entry number only when Column B has started with Advance Paid as shown in excel sheet.
2) Column R has got 2 formulas up to Row 23 Should work only when entry number is available. eg : =IFERROR(VLOOKUP(K22,Master!$B$2:$C$16,2,0),LOOKUP(2^15,SEARCH(TRIM(SUBSTITUTE(B22," ",)),TRIM(SUBSTITUTE($Z$2:$Z$16," ",))),$Z$2:$Z$16))

3) If Entry number is not available eg : has not started with Advance Paid then formula mentioned in Row number 24 eg : =IFERROR(LOOKUP(1,MATCH("*"&$AC$1:$AC$2&"*",B24,),$AD$1:$AD$2),"")
should work.

Instead of copy pasting it sapratly can you please merge both formulas together and when entry number is not available fromula mentioned in point number 3 should apply.
 

Attachments

  • excel forum.xlsx
    19.6 KB · Views: 1
Thanks a lot sir,

Just one Final thing How to Put Advance Paid on Vehicle Number MH04 FU 1575,

Eg : Vehicle Number is MH04 FU 1575 I want to put Advance Paid before all advance payments...

Your Knowledge is excellent sir.
 
Hi:

I am afraid that I understood you correctly, can you be more specific.

Thanks
Dear Sir,

Your formula is perfectly working, only the whenever I will pay Advance I will have to put Manually Advance paid before those vehicle number.

But if there is any formula by which we can simply write Advance paid wherever Applicable instead of writing on each and every single advance.
 
Hi:

Is there any flag which indicates advance pay, if not you will have to create a column which indicates whether there is a advance payment or not. You may not need a prefix to your registration number if you have a column which indicates if advance payment has made or not, the formula can be modified accordingly.

Thanks
 
Dear Sir,
In Master Sheet we have given range up to 16 row which I need to edit as soon as data goes up and down, can you please help me to select entire sheet or up to 500 rows where I no need to edit range manually.

I Tried my level best but no result please advice.
 

Attachments

  • excel forum(1).xlsx
    20.1 KB · Views: 2
Hi:

Find the attached, I have change the range , the formulas I had given was array formula you will have to execute it by pressing Control+Shift+Enter, normal enter will not work.

Thanks
 

Attachments

  • excel forum(1).xlsx
    20.3 KB · Views: 3
Back
Top