LOOKUP function return value

Mike Smith AGT

I have not been able to get this to work, have others?  I have tried a few different methods but cannot the "range" to grow dynamically when new lines are added through a form.  This a critical function that I need to find a solution for.  Here is the procedure I am trying to create funcationality for.

- Weekly Data is entered through a webform

- This inserts a new row at the top of the sheet (can do bottom as well, but prefer the top).

- The most recent data data needs to be linked to a Sight widget

- Data also needs to be connected to excel graphs through the live data connector.

I have been able to get this to work on sheets where the data is entered through a copy paste procedure.  In this procedure the range does not need to be dynamic.  But I can't get it to work with data entered through a form.  The range moves down as the new row is entered?

Any ideas?

Thank you!  

Mike

Daniel Stein

Hi Mike,

In order to automatically include newly added rows, you'll want to reference the entire columns of your lookup table rather than just the range. In order to do this, you'll need to type the names of your two columns, separated by a colon.

For example, if the first column of your lookup table is called "ID" and the last column is called "Name" then you would type ID:Name as your lookup table reference. This will reference the complete columns from ID to Name.

If your column names contain spaces, numbers or symbols, you'll need to enclose them in square brackets. For example, if your first column is called "ID #" and your second is called "Team member" then you would need to type [ID #]:[Team member]

One way to quickly accomplish the above would be to edit your current formula by deleting the row #s from your range reference. This will turn it into a complete column reference.

Hope this helps,

Daniel

 

Mike Smith AGT

Daniel, I will try this, thank you.  I have done that on a number of other formulas.  I can't remember if I tried that already and got a circular reference error or not, but I will try again.

Thanks,

Mike

Mike Smith AGT

Got it!!!  Daniel, thank you, that worked!  I had to make a couple other adjustments to the layout of the information but I was able to accomplish what I needed.  This is a big deal for our project managers to be able update specific items weekly, and create a running log (weekly) of the same data at the same time.

Thanks again!!!!