SOLVED

# Help with formula to pull inventory #s from one excel sheet and input into another

Copper Contributor

# Help with formula to pull inventory #s from one excel sheet and input into another

Hi there,

Could I get some formula help please? I have 1 inventory sheet that I need to update with the #s from another sheet.

What I would like to accomplish is:

If an item # in WORKSHEET 1 (column b) is in WORKSHEET 2 (column b), then take the corresponding Net Stock from WORKSHEET 2 (Column F) for that item number and input it into WORKSHEET 1 Column C (Qty On Hand).

Basically, I want to automate updating the inventory sheet.

I've attached samples of what my worksheets look like.

7 Replies
best response confirmed by AL789 (Copper Contributor)
Solution

# Re: Help with formula to pull inventory #s from one excel sheet and input into another

``=VLOOKUP(B2,'[WORKSHEET 2.xlsx]Sheet1'!\$B\$2:\$F\$30,5,FALSE)``

This formula returns the intended result if the ItemNo in column B in WORKSHEET 2 are formatted as in WORKSHEET 1.

# Re: Help with formula to pull inventory #s from one excel sheet and input into another

@OliverScheurich  Thank you. Is there a way to modify the formula so that it also looks for matches within other text? For example, say I'm looking for 1900-1, but on the lookup sheet it is listed as ET1900-1JJ in the cell. Can I have the formula look up 1900-1 within this cell (I.e. ignore the ET and the JJ) and then pull the quantity?

# Re: Help with formula to pull inventory #s from one excel sheet and input into another

=VLOOKUP("*" & B2 & "*",'[WORKSHEET 2.xlsx]Sheet1'!\$B\$2:\$F\$30,5,FALSE)

# Re: Help with formula to pull inventory #s from one excel sheet and input into another

Thank you. This works for the example in my reply above but doesn't work for the standard numbers. For these, I get the #NA error. Is there a solution that captures the information for both? For example: 26803, 26804, etc.?

# Re: Help with formula to pull inventory #s from one excel sheet and input into another

=VLOOKUP("*" & TEXT(B2,"@") & "*",'[WORKSHEET 2.xlsx]Sheet1'!\$B\$2:\$F\$30 & "",5,FALSE)

# Re: Help with formula to pull inventory #s from one excel sheet and input into another

Hmm I can't seem to get this to work. I'm getting #NA for all now using this formula. Thoughts?

# Re: Help with formula to pull inventory #s from one excel sheet and input into another

see attached Sheet14

https://b23.tv/kbKyPsm

1 best response

Accepted Solutions
best response confirmed by AL789 (Copper Contributor)
Solution

# Re: Help with formula to pull inventory #s from one excel sheet and input into another

``=VLOOKUP(B2,'[WORKSHEET 2.xlsx]Sheet1'!\$B\$2:\$F\$30,5,FALSE)``

This formula returns the intended result if the ItemNo in column B in WORKSHEET 2 are formatted as in WORKSHEET 1.