Excel 2007 : Help to Auto-fill price on invoice spreadsheet

[4ngi3]

Hi,

I have an invoice that I use and I want to make it so that when I type in the name of an item that it would first auto-fill the name of the design after I start typing a few letters and then auto fill the price of the item.

I have looked at a few examples in this forum so I have created a "sheet2" and made two colums "Design Name" and "Price" but I have no idea what to do to "link" this sheet up with the invoice. Because when I type in the name of the design in the invoice, the price is not auto-filling.

I'm stumped. I google EVERYTHING and I can't find the answer to this anywhere. The closest I've found is on this forum but after following the
instructions on a couple of posts I'm still unsuccessful. and its really bugging me and hope someone can provide me with some assistance.

I've attached a copy of my invoice (of course I have omitted the private info)

[Richard Buttrey]

Hello,

Autofill, in the way you want it to work, only works when there are similarly spelt words in a contiguous list immediately above the cell in which you are typing. You can demonstrate this if you add the words Eternity Circle and Chain in A17 & A18, then start typing the 'E' of eternity in A20.

So one solution is to add your list of design names in the rows immediately above the 'Design Name Cell' - you can always hide these rows to preserve the look of your form. Otherwise the only other way would be a macro.

To return the price of your item in F20 enter =VLOOKUP(A20,'sheet 2'!A:B,2,FALSE)

Regards

[Fotis1991]

Hi

Same logic as Richard, suggested you, but with INDEX & MATCH functions.

Hope to helps you.

[4ngi3]

Thanks for all your replies. However, I would like it to automically populate the prices of the item as soon as I type in the design name. I thought I would be able to do it like the attached (I downloaded this from this attached spreadsheet from anothe rpost in this forum). But they didn't say how they did it they just attached the spreasheet as a solution.

Its a similar setup to what I have on my invoice and also i have done the same on "sheet 2" with the name of the item and the price too. I would really like to set mine up like this but I can't for the life of me figure out how the first sheet "links" to the second sheet.

Thanks again

[Richard Buttrey]

Hi,

Have you actually read the responses you've been given, which do offer a solution to the automatic updating of prices?
So I'm not sure why you've attached another workbook which is only showing the same solution you've already been given.

Neither does the second attachment solve your autofill query - and you've also had a response and suggestion to that.

Regards

[4ngi3]

Tahnks for your reply Richard, yes the previous posters have answered my autofill question which I have done on my spreadsheet now and i will be putting the names of the designs above the "design name" column.

However, I tried your suggestion "To return the price of your item in F20 enter =VLOOKUP(A20,'sheet 2'!A:B,2,FALSE)" but that only seems to do that one cell, do i need to type that formula in everytine for each invoice? Because it would be easier for me to just type the actual price. So I have about 15 different designs but I'm not sure what I need to do with entering this formula that you have given me.

You need to explain it to me because I have no idea what I'm doing. I'm trying to follow your directions but I'm also being very litteral in followin gthe directions because I don't really know what I'm doing.

Thanks

[4ngi3]

OK! I figured it out, each row that I go down I have to change the number from A20, to a21 a22 etc!! Just needed to look at it for a little while to understand it. Thanks for your help!

[Richard Buttrey]

Hi,

Even quicker than that. Just click in the cell and drag the little square you see in the bottom right hand corner down as far as necessary.

Other ways of copying and updating other cells is to do a Ctrl-C (for copy), select all of the cells to which you want to copy and then do a Ctrl-V

Regards

[4ngi3]

Great thanks, I'll know that for next time! Luckily I only had 5 cells to do if not it would have taken forever! :D

[Richard Buttrey]

No, fair comment, but when you have hundreds of rows the Ctrl-C and Ctrl-V technique is the way to go (apart perhaps from a VBA instruction).

Regards