Trying to use vlookup with indirect

I have a recipe costing sheet which has a combo box which allows a user to select between vendors. The combo box names correspond to a sheet names for the various vendors and I am trying to do a vlookup to search a range of fields for the name of the product and return its cost per unit.

In other words, you would select a vendor from the combo then to get the per unit cost you would do the vlookup on the vendor sheet (using Indirect) and reference the item name to find the per unit cost and return that value.

Below is the formula I am using:

=VLOOKUP(D4,INDIRECT("’"&E4&"’!&A60:I80"),9,0)

It currently returns a #ref! error and I can’t seem to figure out what is causing it.

I don’t know if its a syntax error or if maybe there is a difference between excel and office. I used a couple of different examples from videos to no effect.

Anyway, any help would be greatly appreciated.

Jim

  1. The separator for sheet names in calc by default it’s ‘.’, if you want to use ‘!’ where is entered as text, you need to set up it in:

    Menu/Tools/Options/LibreOffice calc/Formula - Custom - Reference syntax for string reference - ‘Excel A1’ or ‘Calc A1 | Excel A1’

There is also a global option:

 Menu/Tools/Options/LibreOffice calc/Formula - Formula options - Formula syntax - 'Ecel A1'