Creating an automated receipt

[Niven]

Hi. In collecting funds, I have to give a receipt to the individual. So I am using a template from Excel 2013 which I have customized. I already know from pre-determined rates what amount should be received. On my source sheet I have a list of the people. I want to enter the person's name and it must automatically populate the rate, quantity and total amount received from the source list, on the receipt. I am including a print button which will enable me to print 3 copies of the receipt with two copies having the word "copy" on it. After printing I would like the receipt number to change to the next number. Another sheet has to give me a list of all receipts and the relevant info on it. But instead of having to type each receipt in I want it to pull through from each receipt entered. Is this possible or is this "pie in the sky" stuff?

 

[bstory84]

Yes this is possible. but this is not a 1 formula fix. You will need to utilize Excel's VBE(visual basic editor) to automate the process. you should focus on 1 thing at a time. I would start by using VLOOKUP function to return the rate associated with the customers name. Once you have done that next work on the script to increment and store the receipt numbers. Will need more info to help you with the formulas. like ranges and worksheet names

 

[Niven]

Thanks for that. I did start with VLOOKUP but it seems something in my formula is wrong. On my list I have a few names that have the same first word e.g. South Africa, South America, South Carolina. When I type South it just picks up anything that has South in it. I just thought now I should maybe on my list I should have two columns one with South and the second column with the second word, don't know but I will try.

 

[bstory84]

Whatever you decide will be your reference to the customers information needs to be UNIQUE. It should not be duplicated. this will avoid issues when using matches or lookups.
In the scenario you describe. Vlookup will return a single value for the first match it encounters. so if there are 3 entries that match your statement it will only see the first entry.

Example:

A1 = South America B1=A
A2 = South Africa B2=B
A3 = South Carolina B3=C

Formula: =VLOOKUP("South",A1:B3,2,FALSE) will return a error "#N/A"
Formula: =VLOOKUP("South*",A1:B3,2,FALSE) will return "A"
Formula: =VLOOKUP("South Africa",A1:B3,2,FALSE) will return "B"

Hope this helps.

 

[Niven]

Thanks, it seems to work now.

 

[Niven]

Is there a way to have specific formatting for telephone numbers in a cell e.g. if I enter a phone number it must come up with a certain format e.g. (011) 123 4567. Also usually with receipts and checks we have the amount in words and in numbers. How do I get a number to appear in words automatically instead of me typing it in?

 

[KrazyAl]

Hi

To have the proper format for your phone numbers you can use the built format. Go to Number group on the home tab, select the drop down and then go to Special and on the right side choose phone number.Apply this to all the fields you require the phone number format.

I found this article online that will convert your numbers to text. How to convert a numeric value into English words in Excel

Have a great day.

 

[Niven]

Thanks I will try it out.

 

[Niven]

Hi thanks the formatting works. Except for one thing the "0" before the number does not appear. I understand why it wouldn't because excel takes whole numbers but is it possible to include the "0"? When I enter 0871234567 it appears as (87) 123 4567. Can I have the zero in as well?

 

[Niven]

I managed to create a custom format under format cells and it works fine. Thanks.