Struggling to Reference future cell data in seperate sheet

David Vaughn

Hello all!

I am new to Smartsheet and need help.

I am attempting to take a cell's data (and future data) to populate a seperate sheet. In Excel I would simply enter the following function as an example (=B7*Pricing!E3) then drag the cell function down the column. I see where I can do a manual reference, however, I need this to populate on future data when a new row in the original sheet is populated via forms(without manually creating the reference).

Any help or advice is appreciated.

David

Andrée Starå

Hi @David Vaughn

I hope you're well and safe!

You could use cross-sheet formulas combined with either a VLOOKUP or INDEX/MATCH structure to connect the sheets, and when you update the source sheet, it will reflect on the destination sheet.

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Jeff Reisman

@David Vaughn There are a few ways to do this. I'm assuming you don't have the Premium App called DataMesh which can do this automatically, so we'll go with a formula.

First, you need a value in both sheets that can match, such as an autonumber "RowID" column in the Sheet 1 with a pre-populated RowID column in Sheet 2. Then you'd use an INDEX/MATCH formula in Sheet 2 to lookup the values from Sheet 1. Let's say you want to populate the "Data" column in Sheet 2 with the values from the "Values" column in Sheet 1. As you create the formula below i nthe 'Data" column, Smartsheet will pop up a helper box; use the "Reference Another Sheet" link in the box to create a reference to the Values column in Sheet 1. That will be presented in the formula as a range value between curly brackets. Do the same when creating the match lookup. When you're done, the Index/Match will look something like this:

=INDEX({Sheet 1 Values column reference}, MATCH(RowID@row, {Sheet 1 RowID column reference}, 0))

In English: Get me the value from the Sheet 1 Values column on the row where the Sheet 1 RowID value matches the RowID from this row here in Sheet 2.

Once you are satisfied that the formula works, you'll need to account for rows in Sheet 2 that do not yet have a corresponding match in Sheet 1. Do this by wrapping the formula in an IFERROR to suppress the #NO MATCH errors:

=IFERROR(INDEX({Sheet 1 Values column reference}, MATCH(RowID@row, {Sheet 1 RowID column reference}, 0)), "")

The IFERROR says "if there's an error result from this formula, replace the error message with a blank value (or whatever value you place at the end after the comma and before the close parentheses.)

Lastly, right-click on your formula and at the bottom of the context menu, select "Convert to Column Formula." This will make it so that all rows (and any new rows) automatically contain the formula.

Andrée Starå

Hi @David Vaughn

I hope you're well and safe!

You could use cross-sheet formulas combined with either a VLOOKUP or INDEX/MATCH structure to connect the sheets, and when you update the source sheet, it will reflect on the destination sheet.

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

David Vaughn

@Jeff Reisman

I believe I understand what you are saying and here is the function I have entered:

=INDEX({DH&S WO Request Open Range 1}, MATCH({DH&S WO Request Open Range 2}@row, {DH&S WO Request (Open) Range 1}, 0))

No joy, I am receiving a #unparseable error.