Jump to content

Worksheet IF Statement to get cell text if other cell is TRUE


Recommended Posts

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.

vw IF query.PNG

Link to comment

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.

 

 

Link to comment
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.

Link to comment

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.

Link to comment

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!!

Link to comment
  • 6 years later...

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.

Link to comment

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.

Link to comment

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?

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...