Spreadsheet table auto populate

Copper Contributor

Hi,

I have a table that i'm entering figures into, i'd like to have the table reference a second table (ie: if i enter a value of 600, then that cell looks to the 'master table' and automatically takes the 600 value from that table. Is that possible ?

 

So when i put a figure in of say 1400 under Pressure PSI, it automatically looks to the chart on the right and populates the Torque and Estimate load cells from the chart to the right. Hope that makes sense !

JROZZ_0-1717194381474.png

 

2 Replies

@JROZZ Formulas the provide default values are definitely doable, but there is a side effect.  See the attached workbook.

@JROZZ 

Instead of reading the information from the existing list, there are two other options. With VLOOKUP() or XLOOKUP() you have the disadvantage that only the exact values that are present are found. You will not find intermediate values.


I have no idea how to convert PSI into the other values, but both seem to be linearly related. Therefore, there is also a formula with which you can calculate the values.

The factors appear to be PSI * 55.279 for Torque and PSI * 0.2377 for Load????


The second possibility with this linear relationship is to use the built-in FORECAST() function. This would look like this: (in my example, the value for PSI is in cell E3)

 

Torque
=IF(OR(E3="",E3<=0),#N/A,FORECAST(E3,{27639,99503},{500,1800}))

Load
=IF(OR(E3="",E3<=0),#N/A,FORECAST(E3,{118.848,427.858},{500,1800}))

In both variations, you no longer need your auxiliary table.

Such a simple function is ideal for creating a user-defined function with LAMBDA(). In my enclosed example, I have used the functions: TORQUE() and LOAD() with the forecast function.


Good luck!