Ryan Seybert Posted July 10, 2020 Share Posted July 10, 2020 Hi All, Is there a way to hide zeros in worksheets like in excel? I'm looking for a way to make the data in cells that have a value other than zero more clear. See attached photo. Thanks. Quote Link to comment
Boh Posted July 10, 2020 Share Posted July 10, 2020 I think you would need to add an ‘If’ statement formula to your Data column headers. im crap at writing these but it’s something like: =IF(‘record field’>< 0, ‘record field’, ‘ ‘) 1 Quote Link to comment
Ryan Seybert Posted July 10, 2020 Author Share Posted July 10, 2020 @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. Thanks. Quote Link to comment
Pat Stanford Posted July 10, 2020 Share Posted July 10, 2020 =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 ><. ;-) Quote Link to comment
Ryan Seybert Posted July 10, 2020 Author Share Posted July 10, 2020 @Pat Stanford Still not quite working. It returns a #NAME? error when I plug your formula in. I've tried replacing "***" with other characters with no luck. Quote Link to comment
Boh Posted July 10, 2020 Share Posted July 10, 2020 1 hour ago, Pat Stanford said: @Boh. IF(‘record field’>< 0, ‘record field’, ‘ ‘). The Not Equal should be <>. not ><. 😉 As I mention earlier I’m crap at these formulas...! Glad you @Pat Stanford have jumped in to help! Quote Link to comment
Pat Stanford Posted July 11, 2020 Share Posted July 11, 2020 @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. 3 Quote Link to comment
Ryan Seybert Posted July 11, 2020 Author Share Posted July 11, 2020 @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! Quote Link to comment
Pat Stanford Posted July 13, 2020 Share Posted July 13, 2020 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. 1 Quote Link to comment
Ryan Seybert Posted July 16, 2020 Author Share Posted July 16, 2020 @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? Quote Link to comment
Pat Stanford Posted July 16, 2020 Share Posted July 16, 2020 =IF('Multicable VW'.'Part 1'=125, '1', '') Those are two single quotes next to each other after the last comma, IF(what you are testing, the value if the test is true, the value if the test is false) 1 Quote Link to comment
Ryan Seybert Posted July 16, 2020 Author Share Posted July 16, 2020 @Pat Stanford That makes perfect sense. Thanks. I can use this in a few other places. Quote Link to comment
Ryan Seybert Posted July 17, 2020 Author Share Posted July 17, 2020 @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. Quote Link to comment
Pat Stanford Posted July 17, 2020 Share Posted July 17, 2020 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. Quote Link to comment
Pat Stanford Posted July 17, 2020 Share Posted July 17, 2020 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. 1 Quote Link to comment
Ryan Seybert Posted July 17, 2020 Author Share Posted July 17, 2020 @Pat Stanford Thanks again! The "75ft" was simply for the post. I've gone through my worksheets and changed the units to just be numeric values where I could based on your previous advice. The second string that you wrote worked great. 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.