Create Your Payment Receipt Template Excel for Personnel Effortlessly

Payment receipt template excel for Personnel

hi and welcome back to my channel the previous lesson we have learned how to manage customer invoice payment or in other words the receip vure details which will automatically highlight your mistake you can search for any suppose 2011 is highlighted let's search for it 2011 press enter and it shows us the mistake the amount was 10,000 and the applied amount was 10,1 we just make the changes press enter and the Highlight is gone you can highlight your search just search for 203 and that is automatically highlighted and even you can receive the amount against opening balance or any invoice the invoice status is updated automatically you can see here partially paid fully paid or if it's not paid and here it will show you the opening balance of that customer and the amount that has been paid against the opening balance in this lesson we're going to learn how to print receip Watcher because we have created these receipt Watchers we have want to print it and create this fully Dynamic receipt Watchers the last receipt Watcher is automatically displayed but you can search for any previous receipt watcher that you want and a message will also be automatically showed here that this is not the last receipt Watcher so do you don't make a mistake and print the same receipt Watchers again if it is cash then it will automatically clear these fields and they will change into sterex so let's see how we can do it before proceeding into the lesson let me remind you the links to the previous lesson will be shared in the description so you can watch the previous lessons because all these lessons are related to each other I've already created the sheet and you know how to create sheets like this and I'll walk you through each and every step how I have done it but designing this live at the same time will take a lot of time to save the time I have done it so I've selected the range from here from D4 all the way through L1 I've selected this range I removed the field from it and I've let the First Column here as a border and here also I've left the last column as a border at the top row I've left it as a border and at the end the row number 20 I've left it as a border and so let's start from here the first thing I've done is I've merged most of the C I've have not Center aligned them just I've used the merge so that they always stay in the center even if I resize them so the first fifth row E5 to K5 I merged the cell and I entered the heading it is bold and italic and the font size is 24 and Roman Times New Roman so you can select that in the second row row number six the same thing I've done I've merged these cells and I've centered them and I've entered the address in the third row I've entered my YouTube channel address and in the fourth row the eighth Row the same way I have merged them and I've typed my mobile number here so this is the heading for your company and I'll show you how to insert a logo click insert select a picture and from your file select the logo that you want I have already created a logo here click insert here a picture will appear now you can resize it as you want ing to the size you want but the main thing is sometime you want to fix it you can see this it's fixed here let me select this Str and get highlight the top border and select this range and highlight the lower border that's that's the easiest thing you can do to to fix it in the center you see this picture lies straight in the center I'll just drag it here resize it picture this way you can align your image your logo I just remove it and I'll remove the Border because I don't want the borders no border I'll remove the border from here also so this is the heading you can create and insert a logo and here below that I have written the heading receip Watchers select this range and click on the border that is below so this way will get a line here complete line that will seate the heading from the receip watches and just below here I've written this receip Watcher number and in front of in the nextow F I've created the lower border so we'll get the receipt voucher number dynamically from our receipt voucher here we have created the receipt voucher you see here whenever a receipt voucher is created I want it to be displayed dynamically I don't want to do any manual entries so for that purpose I'll go back to the receipt voucher just before starting the formulas let me explain all the rest of the field so here it is the reip Watcher there is will come will be the retion number and just in column J there will be the date and I just right aligned it and bold and here will it will be the customer ID and the customer ID number and here I'll enter the amount and the amount that the customer has paid received from the name of the customer from whom we received the amount I want to merge and center them so for that purpose I'll just hold the control key and select this range so you can merge multiple cells across and select these cells while holding the control key select this cells also go to merge and center merge across click on it and they will be automatically all these rows will be merged hold the control key and select them I want them to be left aligned here we'll write the name of the customer and this for the purpose why we have received this amount was it the previous balance was it for the current balance or was it ADV once and here we will enter the amount inwards so I'll just merge these cells also hold the control key and select them and here this Bank also click on merge merge across so this way they are also much and this is the bank name if it is a check or online transaction the name of the bank and the reference number if it is a check the check number and who has received the amount and here will be the signature of the person who has received the amount and here you will have have mer these sales here you can write any welcoming message and in this row we'll insert a dynamic a date and time the time whenever you print The receip Watcher the date and time will be inserted dynamically so let's search for the last invoice we'll use the max formula equal to Max press tab select the receipt Watcher I'll select this column that contains the receipt Watcher numbers select long range 999 press enter this where I get the last receip voucher now I want to format it I want to custom format it so I get a leading zeros I want my receipt voucher to appear in as a five digits so I'll just press control 1 go to custom format and here here I'll type two leading zeros and then three hashes 1 2 3 so you see the format you can see the sample here it automatically whenever my receip Watcher will be displayed it will have two leading zeros click okay so this way we get the receip Watcher number formatted now I want the date but always in the previous videos you have SE that we go back and forth to find the column index so this time I'm going to show you a trick how you can display the same workbook in two windows so we'll go to view click on new window and now click arrange windows and from here select horizontal click okay see the same file is open in two windows now and we'll just arrange it because here the re so I'll just arrange this one first I'll just resize it because I want only the column numbers so see here I can easily now see the column names and I'll just resize this one also and here I'll display the print reip watch see here but it makes a slide changes you see the grid lines appear here you can go to view and hide the grid lines to get the date of the receipt watch I'll use the view look of formula equal to V lookup press tab re watch number is the lookup value comma the table and array is here from here I'll go to reip vouchers and select this range I select a long range 999 comma and the column and index is just behind the buttons here in D like is the date comma and I want the exit match zero press enter I get the date here let me change this back to receip vouchers so I can see the headings now now I just want to format it to short format that is ddmm y y y so you can do it two there are two ways to do it easiest way is go to the general from here select the short date and the other way to do it is press control 1 and here select click on edit and click custom and from here if you want to remove the y y you want only 23 you'll get 23 in the custom you can select any format you want I'll so the date is formatted the next thing is the customer ID I'll just copy the same formula from here crl C press Escape click here and press contrl V now I just have to update the column and index so the customer ID is in column e you see easily I can see here it's column E I don't have to go back and forth just type e here press enter I get the customer ID and I want to custom format it I'll press control 1 go to custom and I'll just enter here leading two zeros and static for the number because I want two leading zeros here also click okay I get 04 now for the amount I'll just past the same formula very easily control V and I'll just update the amount is in column M you see easily I can see the column numbers here so I'll just update this from here from D to n that's it it's very easy you can even if you want to tell two different sheets you want to see see whether the amount is correctly entered you can also use this arrange Windows sheet option to see to different sheets at the same time or the same sheets at the same time so the let's format the amount I just click here for the Thousand separator and two decimal places now receed from from whom I have received this amount I'll just paste the same formula here also control V name is in column F just change the index number to F press enter I want the address also for that I'll join it with the address type and double codes comma space double code and control V the address is in column G I'll just change this e to G press enter instead of comma enter here at Dash that looks more better I'll just enter another space delete this comma Dash press enter you see I get here the name of the customer and his address here also now I'll just paste the same formula for why received so for the purpose of receiving I've written here the notes it's a previous dues I'll just copy this formula contrl C press escape and here I'll paste it control V now the reason for is the notes I'll change this to o press enter I get the reason for here the amount in words here I used a Visual Basic query I've already created a function that converts the amount from numbers into words and that is equal to Tab and just select this cell so if you're familiar with VBA you can easily Google it press enter this way I get the amount in wordss received in how it was received in cash or check or online I'll just copy this formula again contrl C press Escape I'll just paste it here crl V and received in the type is in h so I'll just update the column and index H press enter now the bank just paste the same formula control V the bank is in column J so I'll did this from e j press enter I get the bank and the reference number is here in column i crol v i you have to copy the formula again crl C press escape and just paste it here contrl V now the uh reference is in column I I'll update this from J to I this way you can easily find the column index numbers that in which column the field is there it is very easy to watch the same file in two windows press enter I get the check number here and these fonts I've changed to italic but this I have not changed italic it's number so it should look like that and received by who received the amount just click here control V and it was received by uh Naim so it's a column M just click here CH delete and press enter M press enter I get the name and this I left for the signature who has received the amount the next I've merged these CS also you can addite the welcoming business thank you for your business here I want the date and time to be entered dynamically so at the time of printing it should insert the date and time automatically for that I'll use now press Tab and enter this way it enters the time dynamically let me close this window now because I don't need it anymore and let me maximize the window now what remains is the search we want to search so whenever I search for a uh specific receipt Watcher I want the details to be displayed here for that I'll use the offset formula to create a drop- down list because offset formula will update the drop down list automatically as I enter more watches equal to offset press Tab and the reference is the receipt Watcher this from here from the re receip Watcher number comma and the row I just want to move one row below that is from here I want to start one and for the column I it is the same column so I'll just leave it with a comma and for the height I'll use count a function count a press Tab and from this one I want to count this range I'll select long range 999 and close the bracket press enter in the previous lessons I've explained in detail how offset works so you can watch those also those lessons also the links to those lessons are shared in the description now it shows an error but it's actually it's not an error because Excel cannot show more than one value in a single cell just click in the formula bar press F9 here you can see the list of the rece watches press escape to leave the formula just select this formula contrl C press Escape now select data data validation select list and paste the formula in the source control V I want an input message also so here I I'll type reip voucher list and here I'll type an input input message select RV number from the list click okay so whenever someone select this cell it will display the message now I'll select any number from here I want the details of this this receip vouer to be displayed dynamically here for that I'll to make some changes in this formula I'll tap if press tab this search cell is not equal to blank double quat double quote comma then it should return this search number comma else it should continue with the formula press enter I get 203 and I get all the details of that re watch let me select another one you see I'm getting all the details here automatically if it is cach I want these fields to be filled with sterex for example so I'll use if formula if tab this cell is equal to double quote cach as it is text so we have to write in double quotes comma then it should fill these cells with sterex double quote interfuse sterex double quote comma else it should continue the formula press enter see this will appear here I'll copy the same formula from here contrl C press escape and update this formula also control V press enter now as I change to another receip Watcher see now the numbers will appear but if it is cash it will appear in ster so this we have completed our rece Watcher number now I want to print it as I select this press control P see the select the receip voucher is not selected and I want it to be selected dynamically I'll select this range The receip Watcher only and go to page layout print area set print area and you see a dashes around it that means this area has been selected now now I when I press control P see it appears automatically but you can set the mar as too much at the top go to page setup from here margins you can increase the top margins click okay this way whenever I press control P my recept vouer will be automatically displayed I don't have to fix the print areas again and again here I want to display a warning message I've just merge this cell and I selected the rep test text so the message appears in the center suppose if this is not the last receip Watcher I want to display a message here a warning here that this is not the last receip Watcher for that purpose I'll first find the last receip voucher equal to Max press Tab and go to the receip voucher I select this range select long range 999 press enter I get 205 and this is not 205 this is 2011 that means I have searched it I'll just use a formula here if tab the value that is being returned by the max formula is not equal to the rece V number that is displayed here then it is show a message that means it is a search double quote this is not the last rece voucher clear the search double quote comma else it should return blank double code press enter see now I get blank here if I search your 2011 I get the message but I want to change the background color also to White for that I'll use the condition of formatting new rules using a formula equal to this cell is not equal to blank then it should format it I just fill with white color click okay okay you see now as I remove the search from here so it disappears this way we have completed how to print the receipt watch the next lesson I'm going to show you how to create a customer statement a complete customer statement that will be printed very easily you can complete all the customer statements the previous balance will be displayed and the balance ing to the days all this will be created in the next lesson hope you like this video and don't forget to share and subscribe the channel thanks for watching see you in the next video