Forum Discussion

JROZZ's avatar
JROZZ
Copper Contributor
May 31, 2024

Spreadsheet table auto populate

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 !

 

Charting
Excel
Formulas and Functions
  • dscheikey's avatar
    dscheikey
    Bronze Contributor
    Jun 01, 2024

    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!

     

Resources