May 19 2021 11:36 AM
May 19 2021 11:36 AM
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.
May 19 2021 12:01 PM
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.
Once that's been done, your LOOKUPs should work fairly easily.
May 19 2021 12:36 PM
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.
May 19 2021 12:50 PM
May 19 2021 05:38 PM
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.
May 20 2021 12:25 AM
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:
then press ctrl+alt+F5 to refresh the search screen
then it will show the stats related to the part number and the row its located in the inventory table