Referencing a worksheet cell with a data tag

[AStein]

Hello!

 

I have a big, complicated worksheet to calculate individual beam loads for me and I'd like to represent this nicely on my drawing. 

 

Is there a way to visually and dynamically reference an individual cell from a data tag?

 

My backup plan is to create a new worksheet for each beam because I can reference worksheets from other worksheets, but this is very cumbersome.

 

Thoughts? Thanks ahead of time!

[Pat Stanford]

If you are using Worksheet Cells then you can use a #WS_ function to return a cell value into a data tag.

 

#WS_Concat('Worksheet-1':A1)#

 

I used Concat because it will grab the text from the cell and display it as text even though it is only a single value.

 

If your output is numbers and you need to have specific formatting, you might be better off using the TXT function instead as it provides options for setting unit and data formatting.

 

Enclose the worksheet name in single quotes and then follow that with a colon and the Cell Reference not in quotes.

 

Worksheet function reference here:  https://developer.vectorworks.net/index.php?title=Worksheet_Functions#General

 

[Pat Stanford]

And a note on the above.  If the cells you are trying to reference are in database subrows, the above will not work as you can't directly access a subrow because they are "dynamic" and the sort order (and the cell reference) could change based on adding or removing objects from the drawing.

 

If you have to use a database, it might be possible to use a Worksheet Script to locate and access the data, but the script would have to live in every file you want to have the funcationality in or be stored in your user / workgroup folder and updated every time you upgrade VW.

[AStein]

@Pat Stanford

Worked like a charm Pat thank you. I ended up doing a ROUNDUP function instead of TXT to keep it as a number, but I don't know if that'll matter or not at some point.

 

My question now is if there's a way for me to pass the user entered tag field into the formula WS coordinates?

 

So for beam 1, the worksheet cell I want to grab is B4 and add 3 to it, which will point the tag to the right cell.

 

#WS_CONCAT('Beam Weight Calculator':B4)#'#'

 

That's my current function

 

#WS_CONCAT('Beam Weight Calculator':B"Beam # + 3")#'#'

 

And this is the type of thing I'd like to make work. When I put single or double quotes around the characters in the cell coordinates if breaks the function so I'm not sure if there's a function to pass text in or something?

[Pat Stanford]

My standard answer: No and Yes.

 

No. I don't think there is a way in data tag notation to dynamically do the math to determine a cell value like you are trying to do. There does not seem to be a way to force a calculated string to be treated like a function.

 

If you only have a few Beams, it might be reasonable to do a series of nested IF statements, but more than 5 or 10 and your string would probably exceed the maximum formula length.

 

Yes, Maybe you could write a worksheet script that would be able to do what you want. But that script would have to live in every file or be stored in your user or workgroup folder and moved/upgraded every time you upgrade VW.

 

 

[AStein]

Alright, thank you. If I can't think of something clever I'll go ahead and make 20 different ID tags, one for each beam and keep them in my boneyard file.