Forum Discussion
Inventory/Sales data fill
So here is what I am trying to do. I have 2 worksheets Inventory and Sales. I want to input a SKU (Column C on Sales,Column A on Inv) on the Sales Worksheet and have the COGS(Column G on Sales and Column N on Inv) and Location (Column O on Sales and Column O on Inventory) fill in on the Sales Worksheet
Hello,
you can do that with a lookup function, for example Vlookup.
Assuming that the first row in the Sales sheet has the column headers, put this formula into cell G2 to retrieve the COGS
=vlookup(C2,Inventory!$A:$O,14,FALSE)
This will find the SKU entered in C2 in the Inventory sheet in column A and will return the value from column N (the 14th column) of the row with the match.
Likewise for the Location, where you can use the same formula, just change the 14 to a 15 to return the 15th column.
=vlookup(C2,Inventory!$A:$O,15,FALSE)
Then copy both formulas down.
Does that help?
Hello,
you can do that with a lookup function, for example Vlookup.
Assuming that the first row in the Sales sheet has the column headers, put this formula into cell G2 to retrieve the COGS
=vlookup(C2,Inventory!$A:$O,14,FALSE)
This will find the SKU entered in C2 in the Inventory sheet in column A and will return the value from column N (the 14th column) of the row with the match.
Likewise for the Location, where you can use the same formula, just change the 14 to a 15 to return the 15th column.
=vlookup(C2,Inventory!$A:$O,15,FALSE)
Then copy both formulas down.
Does that help?
That is exactly what I needed. Thank you So much. One last question ? How do I get the cell to be blank until something is entered instead of the #N/A ?
Thanks,
Duane
You can wrap the formula in IFError
=iferror(vlookup(C2,Inventory!$A:$O,15,FALSE),"")
