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


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!


Best Answer


  • 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:

    I hope this helps!


  • 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
    Paul Newcome ✭✭✭✭✭✭

    How are you creating your cross sheet references?

  • 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"

  • 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
    Paul Newcome ✭✭✭✭✭✭

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

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!