Ask Your Question
0

Trying to use vlookup with indirect

asked 2016-03-13 02:08:52 +0200

manwenc gravatar image

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

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2016-03-13 02:31:13 +0200

m.a.riosv gravatar image

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'
edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-03-13 02:08:52 +0200

Seen: 163 times

Last updated: Mar 13 '16