Friday, July 15, 2011

yogi_Create An On-Line Price Calculator


Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
gatewaygreening said:
How can people interact with an embedded spreadsheet?
I've got an embedded spreadsheet here:
http://www.gatewaygreening.org/our-programs/resources/vegetable-market-price-calculator.html
It's essentially a caclulator, and in it's original form people can type in a vegetable, the poundage, and then see what the market value is. Is there a way to allow people to type into this spreadsheet to produce those calculations with the embedded version?
--------------------------------------------------------
One will not be able to type directly in the spreadsheet. One way to utilize the produce and price data in your spreadsheet is to create a Form through which a user can submit the Produce Hrvested and Pounds Harvested ... then calculations can be performed in the ResponseTable sheet and the results from the last submittal can be presented via a sister sheet that sort the data in the ResponseTable in descending order and displays data for only the last submittal.

In the following I present the Form through which a user submits the data


The data submitted from the Form is logged into ResponseTable where in some additional columns perform the needed calculations as shown in Sheet1 which also uses the data from Produce Harvested and Market Price data as presented in Sheet2. Finally the data from the last submittal only is displayed in Sheet1a. It will need a refresh of the browser for the last submitted data to be displayed in Sheet1a embedded in this blog post.
Sheet1 and Sheet2 can be optionally hidden so that only Sheet1a will be displayed, and if so desired the embedded sheet code can be modified to display only the contents of range A1:E2 without displaying the Spreadsheet title and the sheet number(name).

2 comments:

  1. The reason I suggested adding FirstName LastName in the Form is to make sure that when the Form submitter looks at the information returned by the website is for the same person and not from an earlier submittal.
    If one is concerned about privacy, one does not have to use one's real name one can create an ID for oneself and use that if that would make one feel more comfortable.

    Cheers!
    Yogi

    ReplyDelete
  2. correction:
    In the list of produce with the associated pricing ... an item was listed as Choy,Pac/Bok where as in the Form in the list of items to pick from it was listed as Choy,Pa/Box ... so I have corrected this to read Choy,Pac/Bok

    Sorry for any confusion that this may have caused to a user using the On-Line Calculator.

    Cheers!
    Yogi

    ReplyDelete