Use an external matrix file with numbers to use input with widget

Hello is there a way to use an external matrix file with numbers to use as

input with the " Calculation" widget or some alternative.

You can perform calculations with values obtained from an Input Table(matrix) field, please check this guide: https://www.jotform.com/help/343-How-to-Perform-Form-Calculation-in-the-Matrix-Field 

Iam not sure if this is the answer for me. to be a little more clear  i have a spreadsheet,

-a matrix spreadsheet were top row.A and Column.A are items

-the other rows and columns are numbers (like an excel lookup table)

At the form I want 3 blank fields, the first 2 fields are the fields were the user can fill in the blank fields. The 3th renders the output.

example

Field.1 is one of the choice of Row A

Field.2 is one of the choice of Column B

Field.3 renders the output of field A en B which is

a static number in the spreadsheet.

Thanks

I do not think this is possible. You can set conditions for the output fields or you can use the form calculation widget. But, you can not pass values to conditions/calculation widget from the spreadsheet.

I can suggest you set the Update Calculate Field condition/Form Calculation widget according to your spreadsheet.

Smart Forms Using Conditional Logic

How to Insert Text or Calculation into a Field Using Conditional Logic

How to Perform Form Calculation Using a Widget

Give it a try. If you encounter any problems, feel free to contact us. We will be glad to help.

This does not help at all Gizem and BDAVID.  I looked at the your calculations options alternative and it does not help. Maybe calculations is the wrong way to approach.

Here is a very simple example what I need:  I have a matrix worksheet containing items and a price list and I need some kind of look-up function to render the price .

----------------------------------

The matrix looks like this; 1e row gives  A , B , C  and         1e column gives A, B, C, rest are prices.

The Back-end worksheet (example matrix price list)               A     B     C

A     11    23    35

B     21    33    45

C     31    43    55

----------

Front-end so field 1 and 2 are filled in by user                        

1e field  B   (filled in by user for example)

2e field  C   (filled in by user for example)

3e field  43  (renders automatic absolute price result)

You have hundred of variations to do math calculations in relation with other fields and 100+ apps and extensions to extend calculations and form use. I would reckon since most of the above options are mainly used by a minority in comparison to a matrix price list which is used by a majority that there must be a simple option for this also.

Please tell me that you can do this. 

It is not possible to use an external sheet in conjunction with the form in order to produce calculation values.  There would be no way to connect the sheet to the form.  The closest thing we have that allows for an external document to interact with the form would be our submissions to spreadsheet widget:

https://www.jotform.com/help/442-How-to-Use-the-Spreadsheet-to-Form-Widget

But that only uses data from the sheet to populate form fields. 

For your purposes, it would require using the tools available in the form.  Setting up the calculations and such in an input table within the form, rather than in an external document would be the only option.

No problem but it still needs to be a cross reference from 2 input fields, like selection of top row and 1e column with a 3th output field of the price, like:

        A     B     C

A     11    23    35

B     21    33    45

C     31    43    55

Is that possible? I do see table examples made up with 2 reference as input fields with a formula. But those are references to first top row and 1e column as input combined with a +,*,-, sign. Those formulas are restricted to ad or multiply the selection as a 3th output

Fore example when you select B and C and + sign in formula it gives the sum of B+C. But I dont see an option as look-up output in those table setups like

B and C gives 43.How would one do this?

It is still not clear to me what you are trying to accomplish, have you started to work in a form? Can you share the link, so we can take a look?

It seems like you were looking to perform calculations in a spreadsheet, you may try the Spreadsheet widget: https://widgets.jotform.com/widget/spreadsheet 

ok again

Let say you have a spreadsheet for glas as a product the top row and 1e column are for example width and height the rest are the prices. This is a common look-up spreadsheet  for a shop. like:

        100  200  300   width cm(top row)

100    11    23    35   prices

200    21    33    45   prices

300    31    43    55   prices

Height cm (first column)

how to convert this into a table and what formula to use? so that when you fill in for example:

blank field 1 for example 200 (width) and

blank field 2 for example 300 (height)

it will output

blank field 3  which is in this case renders  the price ..... "43".

Please check if this is what you mean: https://form.jotform.co/80917253126859 

Ok Bdavid ! But I need the set up without the table matrix. Because there are sometimes more the 50 items in the top row and first columns a table becomes then to large for the window

A good solution would be to let user have to fill in 2 blank fields to get result for field 3.

like this example.

You can achieve what you shown on your screenshot, you can have two fields and perform the calculation in a third field that can be a Form Calculation widget. 

On this guide you will find everything related to form calculations: https://www.jotform.com/help/259-How-to-Perform-Form-Calculation-Using-a-Widget 

Thanks. 

Kevin I dont think so after reading your link. How would you integrate and convert this spreadsheet data beneath into a formula as screen shot below.

Glass prices:

        100  200  300   400    500    width cm(top row)

100    11    23    35    46      57          prices

200    21    33    45    56      67          prices

300    31    43    55    68      78          prices

400    41    53    65    78      89          prices

500    51    63    75    88      99          prices

Height cm (first column)

(As you can see in the matrix width is filled in by user as '200' see top row, height filled in by user as '300' see column left. This will give '43' as the price.

So the whole matrix table above is the data. Is it possible to convert all the data into a formula so it can be generated into a working calculation, as the screenshot example beneath. Please dont give a link be more specific.

Hello,

I was searching for an option for you, but it seems that we do not have any currently. We do have a Spreadsheet to Form Widget to populate rows of a spreadsheet to form fields, but as I understand the range of prices you have is not very small. So, you will need to add lots of conditional statements or other calculations to automate the process. 

If you know the formula how prices are calculated based on height and width, we could try to re-create it for the form.

Related guide: How-to-Use-the-Spreadsheet-to-Form-Widget

Let us know if you need further assistance.

Thank you.