Auto fill cell value if adjacent cell matches cell in another sheet.

Michael Voth

Hello,

I am working on a construction project with a lot of RFIs and would like our issue tracking logs to reference my main RFI log. All the RFIs have unique ID #s. I want the Issue tracking logs to update the RFI # once it is updated on my main RFI Log.

my main log: 1st column is RFI #, and 2nd column is internal tracking number

from issue tracking log:

The second line from my issue tracking log has 372, which matches 372 on my main log. Can I somehow insert a function that will auto populate the blank cell with the RFI # 2878? I would like function to work for the entire column of blank cells, not just for 372 specifically.

Please help!

Thanks

Paul Newcome

I assume you mean column instead of row? How are you selecting the column? Are you clicking on the column header?

Ben Goldblatt

Hi @Michael Voth,

You can definitely use a cross-sheet formula to return the RFI # based off of the internal tracking. There are a few different ways to accomplish this but, with your current setup, I recommend using an INDEX/MATCH formula:

 =INDEX({RFI #}, MATCH([Internal Tracking Number]@row, {Tracking #}, 0))

The logic behind this is "Return the RFI # from the source sheet if the Internal Tracking Number on this row in the destination sheet matches the Internal Tracking number from the source sheet".

You can use different sheet reference names if you would like but the syntax of the formula will be the same.

• {RFI #} is the "RFI #" column from the source sheet
• {Tracking #} is the "Internal Tracking Number" column from the source sheet

Source sheet data

Destination sheet w/ formula

More information on these functions and references can be found in the Smartsheet Learning Center:

• INDEX Function
• MATCH Function
• Formulas: Reference Data from Other Sheets
• Use @row to Improve Formula Performance

I hope this helps!

Ben

Michael Voth

Ben,

Thanks for taking the time to answer this, I have copied your formula and triple checket it. I keep getting "#INVALID REF" for some reason. the formula is highlighting the "[Internal Tracking Number]@row" in blue, but the two references inbetween the { }, are not highlighted.

Paul Newcome

How are you creating your cross sheet references?

Michael Voth

I typed the formula in above manually at first. Then I tried deleting the column ref names and clicked here:

Then i went to the source sheet and selected the entire Row "RFI #" and likewise with the "internal tracking number"

Paul Newcome

I assume you mean column instead of row? How are you selecting the column? Are you clicking on the column header?

Michael Voth

Yes, I meant column. For some reason it started working now. Maybe I just needed to reload the page.

However, it is populating a RFI # for any cells that are blank as well. Is these someway I can restrict it to only populate if the internal tracking number is a number (it seems to be matching the blank cells on each sheet and imputing a number)?

Paul Newcome

Try starting you formula with an IF like so...

=IF([Internal Tracking Number]@row <> "", original_formula)