Creating Form Based on Spreadsheet Calculations

I have a spreadsheet I would like to use as a form.  It has four user inputs.  The first is a zip-code/location input, the second and third are irrelevant, and the fourth is an average cost input.  I want to take the first input, use that information to load a series of four pre-determined percentages and apply them to the fourth input, and show a table of the breakdown, including the percentages.

I've attached a picture of the primary page of my workbook. I've attempted to use the Spreadsheet widget and the Datagrid widget, but perhaps I'm not doing something correctly.  Can we store these values and use one input to pull the appropriate information?

The second pic attached is one of the sheets in the workbook I use to pull the information from.  The percentages are static on specific sheets within the workbook and I use VLOOKUP to find the information and bring it back to the homepage.

Thank you for your assistance!

Thank you for contacting us.

Are you referring to the form with ID 50604074316144?

Unfortunately, we have not received your images. You can try to attach them directly to the post.

We will probably need to use calculations and standard fields instead of the Spreadsheet and Data Grid widgets.

- How to perform calculation in the form

- How to Assign Calculation Value

My apologies, when I did the screenshots, and pasted them into my original question, they appeared on my end, but must have been lost in the posting.

I have the pics, and tried to make it as transparent as possible.  It's not very complex, just the process of pulling the information that I need which is stored within the workbook.

1. The respondent fills in  B1 - B4.  Once the respondent fills in B1, the information is pulled from another worksheet,  which populates the city and state in C1 and D1.

2. Then the form populates C2 by looking in the index.

3. Then it searches the rest of the workbook (remaining 26 spreadsheets) ...

4. for the percentages to fill in B7 - B10.  Finally, C7 - C10 is a simple multiplication of the value in B4 * the value in B7 - B10.

It may not be the cleanest way to do this, but I wanted each state/zone to have it’s own sheet, if I need to change or add anything.  This was the only way I could get it to work...however, the VLOOKUP seems to be giving Excel to HTML converters a problem. I suppose I could rewrite it onto one spreadsheet...

Thank you, I appreciate your help!

I have created this very simple form based on the function in the sheet: http://www.jotformpro.com/form/50613556240953. But I'm sure this form doesn't fully accomplish the thing you want.

a. If you input "10007" in the Location field. The City and State field will be automatically populated with New York, NY.

This method can be achieved by following this guide: https://www.jotform.com/help/268-How-to-Insert-Text-or-Mathematical-Caculation-into-a-field-using-a-Condition.

b. If "Average Utility Bill" field is populated, the fields under the Services section will be automatically populated.

For example, for the "Air Conditioning" field, we multiply it with 0.01. See below:

Kindly try the form and let us know what are the missing here.

Thank you!

Right, here's the issue:  Each of the additional 26 spreadsheets represent the different states/zones and have different percentages for those four fields. New York's air conditioning may cost of 1% of their total utility bill, while Florida's air conditioning cost would be 27% of their total.  That's the data that I need to find a way to store and pull into the equation, according to the user's location input.  It's an image based survey, based on the Energy Information Administration's survey of energy usage in 2009 (the most recent available).

I suppose I could store the percentages in their own container until called for via the user's location input?  It would be almost the same as what I do now, I'm just trying to understand how to reference it.

Thank you so much for your help! :-)

Since each state has their own cost, you can use a hidden field to capture the corresponding percentage and update its value with conditional logic.

And then use the field in the calculation widget.

Hope this helps.

Let us know if this makes sense to you or if you need more clarification.
Best Regards.

That's great!  I was thinking I'd have to do something along those lines, but I didn't realize I could set it with the conditional logic. 

The City and State fields are, really, irrelevant, I did that to clarify the user's location.  My main goal is the state...and the four sets of percentages to go with each state. Since there's only 26 zones, I can also use the conditional logic to set the states with their appropriate zone, then look up the percentages associated with each zone.

Thank you very  much!  It's still a bit of work, but at least it should be workable!  :-)

On behalf of my colleagues, you are welcome! If you notice any issues while creating your forms, let us know. We will be happy to help :)