livespace josha Posted February 5, 2020 Share Posted February 5, 2020 I'm working on a large project with a lot of pipe and drape. I need to provide some estimates on total drapery and hardware. I have a worksheet going with all the data I need, but I can't seem to get the header to sum everything properly. The header only shows the number of rows (57 in my case), not the sum of the rows below. Here are my columns: Length of pipe and drape run Sliders Upright/Base Total drape count I've had similar issues in the past, but that was down to how the column is formatted (number, distance, etc). I've tried every combination, but can't seem to get it to work properly. Any ideas what I'm doing wrong? Attached is a screen shot of what I'm running into. Quote Link to comment
C. Andrew Dunning Posted February 5, 2020 Share Posted February 5, 2020 In the column header for drape count, try entering "=VALUE('Soft Goods'.'TtlDrpCnt')." 1 Quote Link to comment
Pat Stanford Posted February 5, 2020 Share Posted February 5, 2020 Andy would know better than me, but my guess is that that values in the record are stored as Text instead as a number. It can't add the values of text directly so it just gives you the count of the number of rows. The VALUE() function that Andy is proposing would convert the text value into a number that can then be summed. Quote Link to comment
livespace josha Posted February 5, 2020 Author Share Posted February 5, 2020 Yep, that fixed it. Thanks for your help! Question: A "total" amount seems like a simple request...Is there a simple way to do this while creating a worksheet, and not having to edit the header code? Quote Link to comment
Pat Stanford Posted February 5, 2020 Share Posted February 5, 2020 IF the values are stored as numbers, the total is easy. If they are stored as text then you have to use the Value function. Unfortunately the report generator is not smart enough to know when text objects are actually representations of numbers. So no, no real want o create when you create the report. 1 Quote Link to comment
Andy Broomell Posted August 24, 2020 Share Posted August 24, 2020 @Pat Stanford @C. Andrew Dunning I'm trying to create a worksheet that displays the total "Running Length" of some Softgoods objects for rough budget calculations (summarized by drape height). I've tried editing the header to say =VALUE('Soft Goods'.'TTLSGLngth') based on the post above, however it's creating a new issue: The Running Length field is storing the original value as 9'-4" (for example), but when I switch the header to VALUE it's now using the hyphen in the length as a subtraction sign and changing the value; in this example the length now reads 8'8". The column header is adding the lengths together which was the goal, but of course all the lengths are now wrong... How can I calculate the combined Running Length of multiple Soft Goods objects as a total length without this subtraction issue? Quote Link to comment
Pat Stanford Posted August 24, 2020 Share Posted August 24, 2020 @C. Andrew Dunning will probably have a better solution, but here is what I have. The TTLSGLngth is actually a static text field that somewhere in the bowels of the code is being created based on the length. When you convert that to a value then you get the subtraction taking place. If you have the TTLSGLngth in Column C of a database starting in Row 4, then the following will get you the numeric version of the length. =VALUE(CONCAT(SUBSTRING(C4, '-', 1), ' ', SUBSTRING(C4, '-', 2))) If you Summarize your softwoods, then you have to use the Sum Values option in the Column header containing the above formula to get the value of all the Summarized items. HTH. Quote Link to comment
C. Andrew Dunning Posted August 24, 2020 Share Posted August 24, 2020 The solution @Pat Stanford offered up is a great one. You can even replace the cell reference with "('Soft Goods'.'TTLSGLngth')" if you'd like so you don't need the additional column if you don't want it. Quote Link to comment
Andy Broomell Posted August 24, 2020 Share Posted August 24, 2020 4 hours ago, Pat Stanford said: If you have the TTLSGLngth in Column C of a database starting in Row 4, then the following will get you the numeric version of the length. =VALUE(CONCAT(SUBSTRING(C4, '-', 1), ' ', SUBSTRING(C4, '-', 2))) If you Summarize your softwoods, then you have to use the Sum Values option in the Column header containing the above formula to get the value of all the Summarized items. Oh wow - I would have never known that was even possible, thank you! That works. Quote Link to comment
Andy Broomell Posted August 24, 2020 Share Posted August 24, 2020 As I'm playing around with it more, I'm realizing a much simpler solution... I can use =PERIM to get my running lengths (which is actually a little better because I don't need the diameter of my vertical supports to be subtracted, which is what the "Running Length" field seems to do). Thanks again guys! 1 Quote Link to comment
Grayson Posted March 24, 2022 Share Posted March 24, 2022 Hey Guys, Similar topic - are you aware of any way to round the "TTLSGLngth" total running length to whole inches? Maybe its not possible since its text? I've used Pat's method to convert it to a value without the subtraction but it returns the value in feet with three decimal places. Is there a way to then convert that to feet and inches in whole numbers? Quote Link to comment
C. Andrew Dunning Posted March 24, 2022 Share Posted March 24, 2022 If you don't mind poking around Vectorscript a little you might find the Worksheet and Script in the attached file to be helpful. While the dimensions get reformatted to the closest .XX", that would be easy enough to adjust. SG_Count.vwx Quote Link to comment
Wesley Burrows Posted March 24, 2022 Share Posted March 24, 2022 3 hours ago, Grayson said: Hey Guys, Similar topic - are you aware of any way to round the "TTLSGLngth" total running length to whole inches? Maybe its not possible since its text? I've used Pat's method to convert it to a value without the subtraction but it returns the value in feet with three decimal places. Is there a way to then convert that to feet and inches in whole numbers? I round up to the nearest foot using this: =ROUNDUP(0.5+VALUE('Soft Goods'.'TTLSGLngth'), 0) Then use cell formatting to be a dimension. 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.