unearthed Posted May 3, 2017 Share Posted May 3, 2017 Hi I have a worksheet which count plants (I don't want to use the DB as I understand it can't link with other DBs), but it could count doorhandles or ducks. Is there a way I can use IF (or something else?) to add up totals for each 'botanical name' as per the format in the attachment? My worksheet would have a dozen or more sub-tables like this one and some plants would be repeated across some/all sub-tables. The T units for each record (each botanical name) is what I want to sum. So I want something that'll step thru each sub-table and spit out a list saying I have e.g. 5013 Coprosma Taiko, 7351 Haloragis erecta and so on. Quote Link to comment
Pat Stanford Posted May 3, 2017 Share Posted May 3, 2017 In Excel what you are asking for would be called Lookup or a SumIf. Those don't exist in VW worksheets. The only way to do what you want would be to manually enter formulas to add up the cells based on cell references i.e. =SUM(D6, D127, d....) You might be selling the database functions short. The table you show would be easy to create as a database. As long as you can set the criteria (Layer, Class, etc.) to split out your dozen databases, you could then create a single summary database. It would not take the data from the sub-tables, but simply use criteria so that all of the data in the other tables is included in the total table. You can then SUMmarize the data so you would only get a single row for each botanical name. Quote Link to comment
unearthed Posted May 3, 2017 Author Share Posted May 3, 2017 Thanks Pat, I've been underwhelmed/seriously confused by vw's DB in the past. Your outline sounds promising. I'll come back to this post. In the interim can a vw db read from a vw worksheet? Quote Link to comment
Pat Stanford Posted May 3, 2017 Share Posted May 3, 2017 Yes, a database can read from a worksheet cell, but not (easily) on a line by line basis. If you put a formula of =A1 into a database header cell, the value in cell A1 will be shown for every row of the database. Quote Link to comment
unearthed Posted May 3, 2017 Author Share Posted May 3, 2017 8 hours ago, Pat Stanford said: Yes, a database can read from a worksheet cell, but not (easily) on a line by line basis. If you put a formula of =A1 into a database header cell, the value in cell A1 will be shown for every row of the database. Thanks Pat, Would you be able to point me in the directions of " not (easily) on a line by line basis " solutions. Then I'll go and ponder awhile and see how this can work. Quote Link to comment
Pat Stanford Posted May 3, 2017 Share Posted May 3, 2017 I THINK (I have not tried it so I am not sure) you could write a Worksheet Script (basically a user defined function in a worksheet) that could look at the cells of the worksheet (or probably a different worksheet) and find a cell containing a certain value. It could then get the value of a cell at some position relative to that cell and return that value. Like a said, possible, but not easy. My guess is that to write, test and debug the script you are looking at 8-10 hours of work if you are familiar with vectorscript. 15-30 hours if you are not. Quote Link to comment
unearthed Posted May 3, 2017 Author Share Posted May 3, 2017 Thanks Pat, If I do that I'll put it on here, or somewhere else as a job as I can't code at all and have landscape work coming out my ears. Quote Link to comment
Pat Stanford Posted May 3, 2017 Share Posted May 3, 2017 Why don't you write out exactly what data you would want to pull into the database and what the key to accessing that data is and put at least that information up here. If I get bored I might decide this is a fun project to consider. Things like would this be only in the current worksheet or need to look across worksheets? Where is the data to come from? Left, Right, Below the key? Single piece of data or multiple that need to be summed. The better the description the more likely it will work the first time. The more likely it is to work the first time, the more likely it is to get finished before something else more shiny comes along to disbar... SQUIRREL!! Quote Link to comment
unearthed Posted May 3, 2017 Author Share Posted May 3, 2017 wow, I'll put that up Saturday, or maybe Friday. Quote Link to comment
Brian697 Posted August 4, 2023 Share Posted August 4, 2023 I'm am trying to write a formula where when a class it turned on or off it replaces the data in that cell. Does anyone know if there is a way to do that. I essentially have multiple part numbers for a part and when the color changes the part number changes. Each color is on its own class, so I would love for our Bill of materials worksheet to auto populate the part number when the class is turned on or off. Quote Link to comment
Brian697 Posted August 4, 2023 Share Posted August 4, 2023 I'm am trying to write a formula where when a class it turned on or off it replaces the data in that cell. Does anyone know if there is a way to do that. I essentially have multiple part numbers for a part and when the color changes the part number changes. Each color is on its own class, so I would love for our Bill of materials worksheet to auto populate the part number when the class is turned on or off. Quote Link to comment
Pat Stanford Posted August 4, 2023 Share Posted August 4, 2023 Not going to happen with the built in functions. There is no good way in a worksheet to check on objects other than the specific object displayed in the database subrow. Visibility of a Class is not part of the functions available. You can probably do it using a worksheet script, but it is going to have to be very hard coded to your specific part numbers and classes. Could you use an attached record rather than just class visibility to set the part number? 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.