Page 1 of 1
How to use the GET XLS LOOKUPS function
Posted: Thu Mar 13, 2008 12:17 pm
by rogercai69
hi,
I am trying to use the function GET XLS LOOKUPS('file','tab','x row or col','cell') function to import data from EXCEL,
I have read the VEnsim Manual about this function, and it gives a example
regular lookup((0,0),(1,1))
excel lookup(GET XLS LOOKUPS('test.xls','Sheet2','1','B2')) ~~|
subbed excel lookup[shape](GET XLS LOOKUPS('test.xls', 'Sheet2','1' , 'B2')) ~~|
I am doing the same way, but it is not working.
Any one is happy to show me an working axample? or tell me how to do it?
Many thanks in advance.
Roger
Posted: Thu Mar 13, 2008 1:50 pm
by LAUJJL
Hi
I have used get xls lookups a lot in the past, and it works very well.
I remember to have had a problem, using a french excel version where you need to put in the option, settings, for the excel row and columns, L and C instead of R and C.
Regards.
JJ
Posted: Thu Mar 13, 2008 2:53 pm
by Administrator
Originally posted by rogercai69
hi,
I am trying to use the function GET XLS LOOKUPS('file','tab','x row or col','cell') function to import data from EXCEL,
I have read the VEnsim Manual about this function, and it gives a example
regular lookup((0,0),(1,1))
excel lookup(GET XLS LOOKUPS('test.xls','Sheet2','1','B2')) ~~|
subbed excel lookup[shape](GET XLS LOOKUPS('test.xls', 'Sheet2','1' , 'B2')) ~~|
I am doing the same way, but it is not working.
Any one is happy to show me an working axample? or tell me how to do it?
Many thanks in advance.
Roger
Can you post the model you are testing with (and the Excel spreadsheet)?
Tony.
Posted: Fri Mar 14, 2008 11:23 am
by bob@vensim.com
I am guessing the confusion comes from the text syntax that is used in the Reference Manual. When you see
excel lookup(GET XLS LOOKUPS('test.xls','Sheet2','1','B2')) ~~|
That means you create a variable named "excel lookup" then open the equation editor, then select type Lookup, then enter the equation
GET XLS LOOKUPS('test.xls','Sheet2','1','B2')
The new lookup function "excel lookup" can then be used in equations as
output = excel lookup(input)
and so on.
I hope that helps.
Posted: Mon Mar 17, 2008 10:21 am
by rogercai69
Many thanks. It is working now as I follows your advice. In order to make it more clear, I quote your advice here.
"that means you create a variable named "excel lookup" then open the equation editor, then select type Lookup, then enter the equation
GET XLS LOOKUPS('test.xls','Sheet2','1','B2')
The new lookup function "excel lookup" can then be used in equations as
output = excel lookup(input) "