Linking data from one sheet to another

[geefer]

Hey guys. Not that well versed in Excel but my boss would like an easier way to write out invoices. If I can get some help I will gladly pay for your time or donate to a charity on your behalf.

We currently just type out all the customer's info while they're standing there, then print and give to them.

I'd like to be able to just type in the first name and have it auto-populate with what they bought, price, address, etc.

We already have one sheet with all that information on it.

I deleted all the personal information and just left the headings at the top so you can see what cells hold what info. Whenever I try to cut/paste our invoice into Sheet 2, it will freeze my computer upon trying to save, so I have 2 attachments. One being the invoice, one being the database of customers. I can't save the invoice as a .XLS file for some weird reason, so I saved it as an Open Office Document.

I'd like it so when we type in the person's name, it will auto-populate the following:

Telephone
Address
City, Province
Postal Code

And I think that's enough. If anyone would like to have a crack at it, I'll host the file below. Like I said, a donation will be made for your time, and please let me know if you're willing to help again in case we have any other questions, or perhaps want to expand on this in the future.

Many thanks from Pat.

[The Exceller]

See Attached!! I will explain in a follow up post right away.

[geefer]

See Attached!! I will explain in a follow up post right away.

I don't see anything attached!

How's that for quick service though?! Name your price man!

[Whizbang]

No pay needed.

Please review the attached. This uses an Index/Match array formula to match the Surname and First Name for a result.

If there are multiple occurances of the same name, then I will need to add another set of criteria to search for the most recent. Is this something you will need?

[The Exceller]

I just saw it didn't attach. Sorry about that. My file was apparently too big for this site apparently. Is there an e-mail address I could send the file to?

[martindwilson]

i dont think you need an array formula
={INDEX(Data,MATCH($B$9&$C$9,Surname&Firstname,0),MATCH($A13,FieldNames,0))}
can be
=INDEX(Data,MATCH($B$9&$C$9,INDEX(Surname&Firstname,0),0),MATCH($A13,FieldNames,0))

[geefer]

The entry works great for their address. It seems like I can make new entries and when I type last name / first name in the invoice it will auto-populate.

Next question is can I have a couple of more things automated? I could also use the Cost Of Wig to go into the Unit Price and Amount field, then the tax and subtotal and total....but here in Canada we just had our taxes merged into one called HST, so I will have to edit the invoice to not show PST/GST anymore. HST is 13%.

My e-mail address is collins.patrick@gmail.com if you want to get a hold of me directly. We provide wigs for men, women and children going through chemo or any other type of hair loss so you're helping a really good cause out, and like I said, I have no problem donating for your time. You all have been a great help.

Thanks from Pat.

[Whizbang]

See the attached.

martindwilson, thank you very much for the formula improvement.

***Edit***
I've updated the attached a few times.

The current workbook now allows for multpile items on a single invoice. Just make sure the Surname and First Name are the same.

I want to add a capability to test the date as well, but that will be in a future version.

[Whizbang]

Bumping this thread because I made a bunch of changes to the file attached to my previous post.

[geefer]

Bumping this thread because I made a bunch of changes to the file attached to my previous post.

Wow. Downloaded that one and checked it out. That's some insane stuff you got going there. I really like having the items and the customer information in 2 different tabs. Makes it a bit easier on the eyes.

For the items, we don't give them the wig name or wig color. We just simply call it a Cranial Prosthesis, then put the price of it.

We do sell products though, like shampoo and wig stands. Things like that. It would be cool to be able to have a sheet where I can enter in our inventory (Name, price) and can then use a drop-down menu on the client's invoice sheet to select them.

Other than that, this thing is perfect. I am amazed at the wealth of knowledge that you guys show, and the compassion to help people out who just aren't as savvy.

[martindwilson]

maybe "salacious syrups"

[Whizbang]

I've got another version at work that takes into account the date (in case of repeat customers). I'll add in your other requests and upload it sometime during next week.