Adding up a User Field in a worksheet

[minotto]

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?

[Jonathan Pickup]

At the moment your database information is being reported as a text field. You need to convert this to a number field so that will total up all of the dollar values. Try:

=Value( Door'.'UserFld1' )

[minotto]

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?

 

[Jonathan Pickup]

Would you like to add your files so we can have a look at it?

[Pat Stanford]

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.

 

[Jonathan Pickup]

oh, i just assumed that the user wouldn't have used $123.45

[Pat Stanford]

Hi JP. You know what ASSUME means, right? ;-)

[Jonathan Pickup]

yes, silly me

[minotto]

Pat, i did try just entering numbers but that didn't seem to work.  I must be doing something wrong.

 

Here is the file.

 

Worksheets.vwx

[Pat Stanford]

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 

 

=VALUE('Door'.'UserFld1')

 

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.

 

HTH

[minotto]

Yes that did it.  Thanks a lot for your help.