Summing line item totals from another worksheet

[unearthed]

I have a worksheet of plant counts by area - see attached. Each block of text in the worksheet is a plant label for one polygon.

 

What I want to do is sum the totals for each species across the different label areas. I want to do the summing in another worksheet so I can send a nice tidy list to my plant supplier and planter.

 

I have highlighted a couple of plant names to illustrate this, e.g. I want to get the total number of sophora microphylla across all labels. I imagine I need an if statement (or similar) in my other worksheet that whenever it comes across Sophora microphylla it goes across 2 columns, gets that number and adds it to a running total - I wouldn't have a clue how to do this but no doubt someone here else has with an inventory or related problem.

 

In the final case there may be ~ 100 labels.

 

I'm using this method as it works best for my sites and far faster and less complicated than the plant tags landscape areas, also this way I can skim backwards and forwards through the different areas in the table. I don't really want to use vectorworks’ database, but I trust the worksheet system.

[Pat Stanford]

VW2018 does not have the functions you need to do what you are asking.  VW2023 had a VLookup function added that might do what you are looking for.

 

[unearthed]

Well, that sounds terminal Pat! I though there'd be one whizzy method I could barely understand, something just functional enough, and several others.

 

I don't get the VW database as it seems you can't reference cells. I've had some very bad experiences with VW failing at the worst times and ways so very wary now of things that A; takes ages to learn, and B; that fail despite the learning, or don't work well enough to justify the time. Unfortunately VW is the best of any software when it comes to plants and landscapes.

[Pat Stanford]

Database subrows are dynamic, so you are not able to reference a specific cell as that cell location could move.

 

Say you had your plant schedule laid out and wanted to reference a price in cell D3.33.  But now you add a new plant to the drawing that shifts everything down so the value you want is now in B3.34.  Or even worse, you have the database sorted by name but then change the sort order so now it is by pot size and instead of D3.33, it is now D3.167.  There is no good way for VW to keep track of all the possible permutations of new objects, deleted objects, and changes to name, sort orders, etc. to be able to always give you the data that is so easy for your brain and eye to find.

 

You can use almost any function that returns a value in a database also in a Spreadsheet cell to pull individual values and sums from multiple records. 

 

Up to you to decide if it is worth the effort, but worksheets are one of the most powerful aspects of VW.