Auto fill cell value if adjacent cell matches cell in another sheet.
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
Best Answer

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

Hi @Michael Voth,
You can definitely use a crosssheet 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

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.


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"

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

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)?

Try starting you formula with an IF like so...
=IF([Internal Tracking Number]@row <> "", original_formula)
Help Article Resources
Categories
Check out the Formula Handbook template!