Jump to content

Cell references in worksheet formulae


Recommended Posts

We use a system of symbols with record formats to make co-ordinate tables for setting out. This worked fine in VW12, but we're having problems in VW2008 to do with references within the formulae. We use excel to make the list of numbers and then copy them in to the first column. As an aside it would be nice if the worksheets had an excel-like feature where you can add the first couple of numbers then select and drag to get the rest.

We then have a formula in each of the next two columns that references the X-centre and Y-centre of the symbols with the record given in the first column. In VW12 we could do that using e.g. =XCENTER((('Co-ord'.'Ref'=A2))) but this doesn't seem to work any more for new worksheets (bizarrely old worksheets still update OK).

See the enclosed drawing with three symbols with records, the first two rows contain the formula and a reference to the cell containing the record number - this has not worked. The last row has the record number entered manually and does work - however with some of our drawings having 600 co-ordinate references I'm not too keen to have to revise each record manually.

Anyone have any ideas?? :)

Link to comment

I don't know about it working in VW12. I just tried it and it doesn't work here.

As far as I know, the 'record'.'field' syntax will only work inside a database row. How else will the program know what object to get the data from.

I also don't think you can pass a parameter to the XCenter and YCenter functions. They only operate inside a database row also.

Create a database row (e.g. 2) and put in the following formulas.

A2 ='Co-ord'.'Ref'

B2 =XCenter

C2 =YCenter

I think that will give you what you want.

As for an excel like Fill Down, if you copy a cell (or a range of cells) and then select a range of cells before you paste, it will paste a repetition of relative formulas similar to fill down. What it won't do is absolutes.

A1 1 1

A2 =A1+1 2

Copy A2

Select A3 to A5 and Paste

A3 =A2+1 3

A4 =A3+1 4

Pat

Link to comment
  • Vectorworks, Inc Employee

Hi Laura,

This should work.

The problem in your worksheet is that the criteria in the XCenter and YCenter functions is comparing string with numbers

The 'Co-ord'.'Ref' field is formatted as a text in your record.

=XCENTER((('Co-ord'.'Ref'=A3))) doesn't work because cell A3 is a number.

=YCENTER((('Co-ord'.'Ref'='3'))) works because '3' is a string.

You should change the format of your record field to an integer.

Thanks

Hugues

NNA

Link to comment
  • Vectorworks, Inc Employee

Hi Pat,

Worksheet criteria functions are use in database with no criteria as parameter (e.g. =XCenter ) since the database provides the criteria.

But you can also use them in spreadsheet cells and pass the criteria of your choice. They will return the value for the object that match the criteria. If multiple objects match the criteria it returns the sum of all values (e.g. =AREA(T=RECT) ; returns the area of all rectangles in the drawing).

Also, the 'record'.'field' syntax works in criteria functions and it find all objects that have this record field attached to it.

Thanks

Hugues

NNA

Link to comment

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...