I am creating a simple Point of Sale System for a store using an excel/libre office spreadsheet.

The spreadsheet has a sheet called "Inventory" which has the following columns:

  1. Name
  2. Price

This holds data like:

  • Scrabble - $5
  • Monopoly - $10
  • Drink - $1
  • Ice-cream - $2
  • (etc, about 500 entries total)

It has another sheet called "Product Sales", which has two columns:

  1. Item
  2. Price

The first column "Item" uses cell validation to create a dropdown box that lets me choose from all of the names defined in "Inventory" (column 1).

I would like for "Product Sales".Price to automatically update to the appropriate Inventory.Price when "Product Sales".Item is changed.

The only way I can think to do this right now is with a huge, unwieldy, hard to edit, and bug-prone if statement. Another way to do it would be with a for() loop, but I can't do that.

Is there some other method I'm not aware of?

  • 1
    Once you have selected the item, couldn't you do a vlookup on the inventory list? – fixer1234 Oct 18 '14 at 20:34
  • Do you want a drop-down like data validation? Where the cell value can only be on the first list? – Raystafarian Oct 19 '14 at 11:35

As mentioned by the user fixer1234, the appropriate function is vlookup"

 =IF( ISBLANK(B6), "", VLOOKUP(B6, Products.A3:D106, 4, 1) )

I had to move the column-to-match to column A, not sure if there's a workaround for that or not. It wasn't a big problem for me.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.