Parts inventory

Copper Contributor

Hello, im getting together a parts inventory. Im searching for a formula that when i receive my parts packing list with received parts i can  enter in my part number and it takes me right to where its located to update new inventory received number. I have been trying to use Vlookup, lookup, xlookup, find, and many more. They don't seem to work for me or they do and it pops up where i need to go then doesn't work again.  Currently i have in =vlookup(block where i enter my part number im searching, section of all my part numbers, section where my new inventory count goes, "false" for true)

this isnt working it isnt bringing up current inventory count nor letting me know if the part number entered in in the parts.

Thank you 

7 Replies

@Shareena 

 

Not sure where you're wanting that formula, what you're entering, where you expect it to look.

 

BUT, a big suggestion, which I strongly recommend: combine those four separate inventory lists into a single, just adding one column (if truly needed) that recognizes the category that is now reflected in the individual tab. It would be a small amount of work.

 

Here is an image of the current column headings, with minimal change (I moved the "Description" heading in the Maintenance category). Other than that, as is immediately evident, they all have virtually identical column headings. So a single database would serve your purpose and make it a LOT easier to search.

mathetes_0-1621450818525.png

 

Once that's been done, your LOOKUPs should work fairly easily.

Thank you for that, here is the only sheet i want to work with once i have it all together.

i will create a section that i search for my part numbers .

@Shareena 

 

A single data table would NOT have all the blank rows. That makes it more readily readable by humans, but you're asking a computer to do the reading.

 

One of the major mistakes made in designing a spreadsheet where you want LOOKUP (in any of its various manifestations) to work, is to make it pretty. Eliminate the colors, eliminate the blank rows. Add a column to take care of (recognize) the different sections.

the bank sections are to add more parts. so you saying to not have the blank spaces?
All i want is my search bar where i enter in a parts number to locate where in the parts # is in line A and line I the current inventory amount.

@Shareena 

And yes, I'm saying no blank spaces. A table is a continuous table. It's a mistake to leave open rows. Here's a very good reference site: note what is said about tables and how to create them. https://exceljet.net/excel-tables

 

The second sentence under the first heading: " First, remove blank rows and make sure all columns have a unique name, then put....."

The mistake is to create a table that looks good to your human eye. This is data to be used, manipulated by the computer: in your case, the source data for an inquiry. Make the results of the inquiry look as pretty as you want. Keep the database itself, the table, just plain and fully functional. You're making it harder to deliver the important result you want, first by having the source data on separate spreadsheets and second by dividing them into sections when you have them on a single sheet.

Hi @Shareena 

Here play with this

you select the part number using a drop down, you can type a few of the number to narrow your search in the drop down list:

for example, you type HK in the drop down then press the drop down arrow:

Yea_So_0-1621495319447.pngYea_So_1-1621495352932.png

then press ctrl+alt+F5 to refresh the search screen

Yea_So_2-1621495412114.png

then it will show the stats related to the part number and the row its located in the inventory table

Yea_So_3-1621495493401.png