You can use lookup and reference functions in
Symphony to make it easy to retrieve information from external data list.
The lookup functions (VLOOKUP and HLOOKUP) allow you to search for a value
in one place and insert it in another.
The HLOOKUP functions looks in rows ( a horizontal lookup ) and the VLOOKUP
function looks in columns ( a vertical lookup )
Each function use four arguments separated by semicolon in the following
definition.
=VLOOKUP(Search criterion;array;index;sort order)
For example, you have a list of order on sheet named "Orders"
and all of your customers on another sheet named "Accounts"
When you type their account numbers in the Orders, you want Symphony to
fill in their email address.
Account No. is the first column in the Sheet, while the Customer Email
addresses in the 4th column.
A VLOOKUP will do this for you.
- In Cell A3, you key in the account number.
- In Cell B3, you click Formula button on toolbar,
then enter function like this:
=VLOOKUP(A3; account; 4;0)
- Click OK when you finished
Now you should define a data range, as Symphony doesn't allow to browse
and pick sheets in another file,
I'm suggesting you use this workaround:
1. Select from menu: Create - Names - Define, Specify range name "account"
2. Type as follows in "Assigned to":
'file:
c:/Sheets/Accounts.ods'#$A.$A$2:$E$10
Absolute Path#SheetLabel.RangeStart:RangeEnd
Make sure you have clicked Add button when you define range name.
FAQ about VLOOKUP:
Q1: Why do I get #NAME? in the cell of functions?
A1: Since you haven't defined the data range yet, or Symphony cannot find
the specific file in absolute path.
Q2: How do I use the 4th arguments? TRUE or FALSE
A2: You may use 1 (TRUE) for the situation when first column is sorted
in ascending order, usually it's faster.
You must be careful even when there's no match found in the range, you
will still get return value.
If the first column in array is not sorted, you may use 0 (FALSE, by default)
When there's no match found in the range, you will get #N/A as return value.