Jump to content

Hide "zeros" in worksheets

Recommended Posts

@Boh Thanks for the reply.


I was under the impression that If/Then statements were not valid in VW Worksheets.


I'm not too savvy with building the custom fields for the reports.  Can you detail that out a little?  The current formula for one of the columns on my work sheet is: ='Cable - DMX Homerun 5PIN'.'25'''. How would I blend the two together.  I get errors when I try what seems logical to me.



Link to comment

=IF('Cable - DMX Homerun 5PIN'.'25'=0, '***', 'Cable - DMX Homerun 5PIN'.'25')


This says if field 25 is zero then put *** into the field otherwise put the value of field 25.


Unfortunately, there is no way to use cell formatting to accomplish this like you can in Excel.


@Boh. IF(‘record field’>< 0, ‘record field’, ‘ ‘).    The Not Equal should be <>. not ><.  ;-)





Link to comment

@rseybert Can you post a sample file? I don't mess with DMX enough to have a sample to try.


Actually, I think I see the problem.  When I copied your original formula, I did not realize that the field name if actual 25' (with the foot mark) and I just gave you a formula for a field name of 25.


Semi-important tangent:  It is always best to not use single or double quote symbols as parts of records or field names. When you do, then you start having to add extra characters to "escape" those symbols and make them usable. In this case you need to add two additional single quotes everywhere you have a field name that ends in a single quote.  ONLY for clarity, I am going to write out how the field name part should be written with a space between each character.


' 2 5 ' ' '


In use it should be '25'''


To my old eyes, I can't tell if that is two quotes or three. Hence my dislike of escaping.


Add two additional single quotes before the =0 and before the final ) and you should be all set.


If not, ask again.

  • Like 3
Link to comment

@Pat Stanford Thanks for that.


This is a worksheet that is getting data from custom records that I built and doesn't use the actual DMX jumpers.  TBH, the cable tools in VW are relatively worthless beyond just drawing the geometry.  The data that is supplied is great but you can't really do enough with it in terms of custom worksheets.  The internal cable paperwork in the spotlight menu don't get granular enough for what I need.  I've spent some time during our industries standstill beating my head against the cable tools and I just can't get them to work the way that I need them to.  To get around them, I've created a few symbols with custom records that populate the data that I can sort the way I need it to.


Anywho... the formula you supplied worked and eliminated the "Zeros", however, it presented a new challenge.  It was seeing each cell in that database column as having a value of at least 1, which was adding to my totals.  I am calculating the totals by referencing the database cell, not each line item.  I'm not able to successfully sum a range of cells like you can in excel and I'm pretty sure its because of the point numbered cells.  By eliminating any characters where the *** was it leaves those cells truly blank and allows the totals to populate correctly.


Thanks again for the help!

Screen Shot 2020-07-11 at 11.52.51 AM.png

Link to comment

Sorry, I should have pointed out that the *** was just a place holder. Like I said, quotes next to each other drive me crazy so if it had written '' would you have know that it was actually two single quotes with nothing between them or would you have thought it was a double quote mark?


Glad you got it figured out.

  • Like 1
Link to comment

@Pat Stanford


OK, I want to take this a step further:


How would I write a string that only populates a value based on specific input and filters out everything else.


For example =IF('Multicable VW'.'Part 1'=125, '1', 'Multicable VW'.'Part 1') says if the "Part 1" of a multicable equals 125 then put the value of "1".  Which is great, however, it also populates the rest of the cells with the value of "Part 1". 


How would I tell it to ignore all other values?

Link to comment

@Pat Stanford I apologize for continuing to bug you with all this.


To take it even further, I'd like to do something like this: =(IF('Multicable VW'.'Part 1'=75, '1', ''))+(IF('Multicable VW'.'Part 2'=75, '1', ''))


Basically, I am trying to add 2 parts of a multicable together to get the quantity of 75ft parts that comprise the multicables length.  A multicable that is 150ft total length could be created with (100ft + 50ft) or (75ft + 75ft) or (125ft + 25ft) and so on.  There potentially could be a third and fourth segment as well.  The database that I am working on will create a multicable list that line items each multicable in the drawing, what parts build it, and how many of what size parts I need to order.


Is something like this even possible?


I think I'm getting close because I'm getting a return of #VALUE! 


Also, can you point me in the direction of where I can read up on the string code?  I cannot find anything referencing this in the VW help or in some web searching.


Thanks again for all the assistance.

Link to comment

You have quotes around the 1 and the 2 which makes them strings instead of numbers. Remove the quotes from just those parts and you should be good to go.


=(IF('Multicable VW'.'Part 1'=75, 1, ''))+(IF('Multicable VW'.'Part 2'=75, 1, ''))


Actually that is not quite true. In the line above it will work fine if you have both parts but if you only have one then you will be trying to add a number to an empty string. Try it. VW might be smart enough to handle it, but I don't think so. You will probably have to do:


=IF((IF('Multicable VW'.'Part 1'=75, 1, 0))+(IF('Multicable VW'.'Part 2'=75, 1, 0))=0, '', (IF('Multicable VW'.'Part 1'=75, 1, 0))+(IF('Multicable VW'.'Part 2'=75, 1, 0)))


The long version returns number for the addition. If that number is zero then the outside IF returns a blank string (double single quotes). If it is not zero then it does the whole calculation again and returns that value.  I may have gotten the parentheses wrong, so if it does not run, check those first. 

Link to comment

Also, you are not going to be able to add 75ft + 75ft.  The unit markers make it a string. You are going to have to figure out how to get 75 + 75 and then if you need it convert the 150 back into a string and add the ft back into the string.


I believe (I don't have time (or inclination) to test right now) that you can use a multicharacter string for the delimiter in a Value call, but rather than looking for the entire string, it looks for the first instance of any character in the delimiter string.


So a delimiter of ' fi' (space, letter f, letter i). =Value('your string here', ' fi', 1) SHOULD return the following:


75ft.    75

75 ft.   75

75in.   75


Test before using. Your milage may vary. Here be dragons.

  • Like 1
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.

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