Marc Davies Posted February 8, 2023 Share Posted February 8, 2023 I am trying to find percentages of areas in database rows against the total value in the header for all areas. However I don't seem to be able to extract the total area values without a circular referencing problem. i.e. I cannot use the total value from the database header row to use in a formula in the percentage values column. This is surely a simple issue, but I am scoring well below "SIMPLETON" for this task. Anyone else solved this recently? Quote Link to comment
Silvano Posted February 12, 2023 Share Posted February 12, 2023 Until the experts arrive: Click on the % column header. Type it: =(click on the areas header)*100/(TYPE the total area) Quote Link to comment
Pat Stanford Posted February 12, 2023 Share Posted February 12, 2023 Not at all simple. I have filed this as a Bug against VW2023. There is no really good work around. The best answer is to use a function using criteria in a Spreadsheet row and use that for the calculation. What are you trying to get the percentage of and I can probably help you come up with a work around. 2 Quote Link to comment
Marc Davies Posted February 12, 2023 Author Share Posted February 12, 2023 Thanks, Both. Silvano, your solution works for Spreadsheet cells but not for Database header row’s unfortunately as it sets up a circular reference. I have tried to reference the Area cell into a non-database row, as a Value, but no joy there either. My temp solution is to ask users to manually type the header Area value into the formula = Area / <<typed total Area value>> which works for each database row with % column set to “Percentage” format. i need to be able to extract the total Area as a value into a spreadsheet cell. I’m sure you will have a solution Pat. thanks, Marc Quote Link to comment
Marc Davies Posted February 12, 2023 Author Share Posted February 12, 2023 Ah, sorry Silvano. Yes, i missed the word “TYPE “ in your reply. That is exactly what I have had to do. 🙂 Quote Link to comment
Pat Stanford Posted February 12, 2023 Share Posted February 12, 2023 Are you looking at a cell that actually has a formula of =Area? Or are you working with something like a Space object and referencing a Record.Field (ie 'Space'.'Gross Area')? Makes a difference in the solution. Quote Link to comment
Marc Davies Posted February 12, 2023 Author Share Posted February 12, 2023 (edited) Hi Pat, formula (=AREA) at the moment, in a database header. The database has criteria looking at a series of polygons, in this instance, with a particular Record set. I might well be looking at Records attached to symbols on another occasion, but my Database criteria will hold the reference to the Records, not the cell formula like your second example. Would it help if I changed my criteria/formulae M Edited February 12, 2023 by Marc Davies Quote Link to comment
Silvano Posted February 12, 2023 Share Posted February 12, 2023 5 hours ago, Marc Davies said: Ah, desculpa Silvano. Sim, perdi a palavra “TYPE” na sua resposta. Isso é exatamente o que eu tive que fazer. 🙂 Marc Davies, as you saw, this solution would be temporary, just to work, because you have to type again every time you change areas. Your problem is important and I would also like a better solution. Quote Link to comment
Pat Stanford Posted February 12, 2023 Share Posted February 12, 2023 So since you are using the =Area function it actually makes it easier. But you will need to put the SUM of the areas into a Spreadsheet cell. Most of the Worksheet functions use the criteria of the database if they are used in a Database row. Those same functions can be put into a Spreadsheet cell and have a Criteria passed to them as a parameter. So right click on the Database header row and choose Edit Database Formula. The formula for the database will display in the Formula bar. Copy everything inside the =DATABASE() function. That is the actual formula for the criteria of the database. In a spreadsheet cell enter a formula of =Area( and paste the copied criteria and put the final closing parenthesis. You should now have a formula that will give you the total area and that will update when the drawing changes that you can use as the denominator of your percentage formula without making it a circular reference. HTH. Ask again if not clear enough. 2 Quote Link to comment
Marc Davies Posted February 12, 2023 Author Share Posted February 12, 2023 Brilliant Pat. That is great. i understand the concept and knowing how i can get Database info directly into a spreadsheet cell will help me in other areas also no doubt. As is so often the case with your advice, it feels like another Vectorworks door has opened to me. Many thanks. 1 Quote Link to comment
Recommended Posts
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.