Jump to content

Adding up a User Field in a worksheet


Recommended Posts

I'm just learning about worksheets and have learned a lot on the forum.  But i can't seem to find an answer to a very basic procedure.


I'm creating a Door schedule and have used the User Field 1 in each of the doors for the price.  With  ='Door'.'UserFld1'   placed in the worksheet database header, each of the database subrows have the correct price and have been formatted with decimal and the leader $.  But the database header row only shows the number of doors with a dollar sign in front.  


How would i show the total price of all the doors in the last cell of the column?  Is there a better way to do this that i'm missing?

Link to comment

Thanks Jonathan.


Just to reiterate in case i'm missing something:

database header -     ='Door'.'UserFld1'

Database cell format - decimal, dec. 2 places and comma, leader $


In the same column, in the next spreadsheet row, I tried entering =VALUE('Door'.'UserFld1')  but got #VALUE! in the cell.  Am i missing something?


Link to comment

It is very likely that you have extra formatting in your UserFld1.  In order for Value to work, you need to have just a number.


12345 works.


12,345 does not work


Even something as simple as an extra return at the end of the line (making it have 2 lines of data) will make it unable to convert to a number.


You actually might be better off creating a custom record and attaching it to your door. That way you could format your price field as a number instead of having to deal with the String representation you get with the UserFlds.


Link to comment

You still need to use the Value function to convert the string representation of the number (as stored in UserFld1) into a number the worksheet can do math on.


Change your formula to 




and I think you will get what you want.


If you want to be able to edit the value in the worksheet, you will need to use two columns, one just as you have it now showing the text representation that can be edited and a second with a formula of 


=Value(N4) that will give the numeric number and can be summed.



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