VLookup format

[kulahblind]

Here's a rundown of what I'm looking to do but I can't figure out how to grab the necessary information from the "Inventory" tab to this one.. [Excel Document attached]

I want to type an Asset Number in B7. And have the information for Make / Model / Description / Location / Date Entered / Owner / Comments pulled automatically for the Asset Number I've specified which is Column A on the "Inventory" tab.

My list is continuously growing so it would need to allow for new rows that do not exist yet.

I looked through the forums and couldn't find my exact need. Any help on this would be greatly appreciated!

[Tank997]

Is this what you need?

Good Luck!

Tom

I removed your : in column A so the names match the names in the headers in the inventory table.

[outofthehat]

You're right about using VLOOKUP. Format is VLOOKUP(What, where is the table of data, which column to select, FALSE-for exact match).

To cater for expanding data, rather than selecting just the current range of data, select whole columns, and you'll end up with, e.g. in cell B4,
=VLOOKUP(B7,Inventory!$A:$T,3,FALSE)

You will run into a problem with the fact that your BF#s are actually stored as text. That means that in B7 you need to type e.g. '01001 (note the apostrophe) to make it work.

Perhaps better would be to have numbers in the BF# column, with custom formatting 00000, then you only need to type 1001 in B7 to make it work.

[outofthehat]

Or better still, make use of Excel's table functionality as suggested by Tank!

His auto-generation of the numbers (e.g. 3 above) is much safer too, as it protects against new columns being added....

Glad Tank's less lazy than me...

[Tank997]

Out of the Hat good point about the text numbers. in the input cell, Because the data on the inventory sheet is in a table using the table as the lookup range makes it dynamic by just using the structured table reference for the range name.

[Tank997]

LOL but you type much faster than I do

One other suggestion I would make is, if possible, move the input row for the Asset to the top of your range, say row 2 or 3 so the two input cell are together it will make data enter faster and will also help with not over writing the formulas by mistake.

[outofthehat]

I guess it depends how the data comes out of the database. If it's coming out as text, you can either use the apostrophe in B7, or instead type a number in, then use TEXT(B7,"00000") instead of B7 in the VLOOKUP ....

[kulahblind]

Tank,

This was exactly what I needed. Thank you for your quick assistance in this matter!

[outofthehat]

I decided years ago to learn to touch-type. Great decision! Mavis Beacon Teaches Typing, c $30 from Amazon! Excellent program.

[Tank997]

Thanks I will check out that typing progrm!

I added an activeX combo Box for you Asset number look up check it out and see what you think.

Forgot the file.