Searching another sheet to plug in values

[needhelp4excel]

Hello,


I am trying to create a sheet for invoicing. I have a sheet I want to use as a database. I want to search the database from the "invoice" sheet and fill the fields once i have found what I am looking for.

attached is a sample database I will use. I dont want to use the whole row, only selected colums. for example short_item description, item_number, Brand, SRP, ect. I want to search on the "invoice" sheet, for example, item description,and fill out the rest of the necessary fields in that row with data from my "database".

what formula or macro can I use to acheive this?

[davesexcel]

Where is the Invoice sheet?

[teylyn]

Hi,

your solution is called VLookup()

In your invoice sheet enter the ItemId in a cell, say A1 = 7645

Then, to find the short description, which is in the 5th column in the database filled with data up to row 100, use

=VLOOKUP(A1,Sheet1!$a$2:$ap$100,5,FALSE)

hth

[needhelp4excel]

Where is the Invoice sheet?

sample invoice now with sample database

[teylyn]

in that case in C5 and copy down

=IF(ISBLANK(B5),"",VLOOKUP(B5,database!$A$2:$AP$100,5,FALSE))

You have to decide which field should be the key and which should be the calculated value. Item number makes sense as the key, since this is how your database is organised, too.

Other solutions are possible. The same formula can be applied to retail price, just change the column number (the red bit in my earlier post). I couldn't identify which column holds the retail price, but I'm sure you will.

cheers

[needhelp4excel]

sorry for the basic references i'm making. but, I would I would like to search the short_desc and fill out the rest of the fields I "will" have in the invoice.

so, If i search in the "invoice" under description I type in "air pump" I want to invoice to be filled out with description, manufacture,item number, retail price,wholesale cost,ect. it seems like vlookup can only search one key and return value. but, I want to search one key and return mutiple values to fill out the invoice. does this make sence, and is it possible?

[MattVarnell]

If I am understanding you correctly, you are wanting to use the "short description" as your Lookup Value. If so, you will need to move the Short Description from Column 5, (E) to Column 1, (A). The VLOOKUP function searches the leftmost column for the match. Secondly, the text that you type in to search has to match exactly in order for it to work. Depending on the number of items you have, it may be a good idea to use Data Validation to select from a list, that way the spelling will match and that will not be an issue.

[shg]

Folks, please don't quote whole posts. It just clutters the forum.

Thanks.

[MattVarnell]

See if this is what you are looking for. I added a test line to show you the change. If this is what you are looking for, then you can use other functions to calculate given quantity and tax to give you a total. You would want to hide Column H once you got your invoice how you want it.

[needhelp4excel]

MattVarnell

yes this is exactly what I am looking for.

how do I code for the data validation?

I'll put together a final version of what I want and upload it.

[needhelp4excel]

here is the invoice layout I will use, along with the final database.

[davesexcel]

It should work, you probably will have to change the workbook name to

Please Login or Register  to view this content.