Forum Discussion

Duane Hale's avatar
Duane Hale
Copper Contributor
Feb 19, 2018
Solved

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 Co...
Formulas and Functions
  • IngeborgHawighorst's avatar
    IngeborgHawighorst
    Feb 19, 2018

    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?

     

Duane Hale's avatar
Duane Hale
Copper Contributor
Feb 19, 2018

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

IngeborgHawighorst's avatar
IngeborgHawighorst
MVP
Feb 19, 2018

You can wrap the formula in IFError

 

=iferror(vlookup(C2,Inventory!$A:$O,15,FALSE),"")

  • Duane Hale's avatar
    Duane Hale
    Copper Contributor
    Feb 20, 2018

    Thanks Again !

     

    Duane Hale

Resources