Get text or number from an external table
The function CELL can be used within lookups and formulae to get text or a number from a table.
To use the CELL function the table must exist and be set with the correct values
A table is represented in the file as follows:-
##DOOR,500,600,800
OAK,12.99,15.50,17.90
GREY,9.00,12.00,15.00
TEAK,11.50,12.50.13.50
This defines the following table:-
Door
|
500 |
600 |
800 |
Oak |
12.99 |
15.50 |
17.90 |
Grey |
9.00 |
12.00 |
15.00 |
Teak |
11.50 |
12.50 |
13.50 |
The values are customised and represent the item that varies with the different settings for the columns and rows. In this example, variation in hinge size depends on Door width and the nature of material for the door. For example, a 500mm Oak door has a value of 12.99 and a 500mm Teak door has a value of 11.50
Cell function format:-
CELL(table name,row name,column name)
To get a value use the expression in a formula:-
CELL(DOOR,OAK,600)
Can also use variables and lookups:-
CELL(DOOR,$STYLE$,@SIZE@)
Cell function in Lookups evaluates to a text value
Cell function in formula evaluates to a number
The following examples show the use of the Cell function:-
In a formula:-
=5.0+CELL(DOOR,@DOORMATERIAL@,@SIZE@)
In a lookup:-
The following table (BKM) describes how back material varies for the style and size of a cabinet.
BKM
|
800 |
600 |
500 |
400 |
Tudor |
HBD06 |
HBD06 |
HBD04 |
HBD03 |
Georgian |
CT06 |
CT06 |
CT04 |
CT04 |
Modern |
PL06 |
PL04 |
PL04 |
PL03 |
The back material for this range is described by the lookup $BACKMATCAB$. In the product this would be set as follows:-
FLOOR CABINET
BKPANEL/1 X-50.0 450.0 $BACKMATCAB$
The Cell function can be used in the Lookup table when defining the back material lookup
Lookup table
Name |
Description |
Value |
BACKMATCAB |
Back panel material |
=CELL(BKM,@STYLE@,@SIZE@) |
When Style and Size are set in Product requirements the program uses the table to select the correct back material code.
For example, if style = Tudor and size= 500mm then back material = HBD04
Formula evaluation (for CELL functions in formula only)
A formula can also be used for a row,column or table value.
=CELL(DOOR,OAK,STR(300+200))
This evaluates to (=CELL(DOOR,OAK,500))
Function STR is used to convert formula into a fixed numeric value - this can only be used with the CELL function.
Notes
- For a formula the cell value must be a number
- For a lookup item the cell value must not be more than 80 characters
- Select the User defined tables option to create and edit cell tables
