Connect with peers, share your expertise, and inspire what’s next in Smartsheet — from proven practices to practical how-to insights from fellow users and product experts.
Sign in to join the conversation:
Hi,
What is the best formula to use for auto populating a same field data from another sheet based on a project ID #? Please advise.
Thanks
I would suggest an INDEX/MATCH. Here's the basics of how this works...
.
INDEX is used to display data from within a range after specifying the row number and optionally the column number.
=INDEX(data_range_to_pull_from, row_number, [column_number])
data_range_to_pull_from: This is the range that houses the data you want to display.
row_number: This is a numeric value that will determine the row number within the range to pull from. If there is only one row, enter 1.
[column_number]: This is optional. If your data range is multiple columns, this would be a numeric value to designate which column to pull from.
MATCH is used to produce a numeric value of where within a range a specific search value is found. The range is treated as a grid. 1 would be the top left cell within the range. It will then go left to right and top to bottom.
=MATCH(search_value, range, [search_type])
search_value: This is where you specify what value you are wanting to search for within the MATCH function. This is where we leverage your unique ID.
range: This is the range that you want to search for your value in. In this case, this will be where your unique ID is housed.
[search_type]: This is optional. I always recommend using the value of 0 (zero) for this as it will look for an exact match. This ensures the highest level of accuracy.
So basically we will be using the INDEX function to pull the data and the MATCH function to determine which row to pull it from based on your unique ID. Since you are doing this on another sheet, I will show it as cross sheet references.
To create a cross sheet reference, simply click the link to "Reference Another Sheet" in the helper box that appears under your formula as you type whenever you get to the appropriate section of the formula.
My example will specify what exactly to select within the range names as "Sheet: Range}.
=INDEX({Original Sheet: Column to Display}, MATCH([Formula Sheet: Unique ID]@row, {Original Sheet: Unique ID Column}, 0))
Hi Paul,
Thank you. I was able to build the index/match formula in one column referencing another sheet column field data. If I tried to build the same formula in the next column and referencing different field column, the formula automatically updated the other column. Do you know why it is doing this? Same with the sumif formula as well. It is quite frustrating. Please advise.
What is your original formula, and how are you building the new formula to reference another column? What is automatically updating?
Is there a way to revert to the old dashboard look and feel. The new dashboards do not look good at all. There is no way of knowing if there are additional items in the widgets until you scroll over one to learn there is more to it since the vertical bar doesn't show. We have hundreds of dashboards and they will all need…
When i try to send or print the timeline view instead of showing the progression bars like they do on the screen it only send the data entered and not a graph type view to print. Is there a way to do this. I am using a mac platform if that helps.
I have reproduced the following behavior in a validated Smartsheet application. User permission: Editor (not Admin) Row status: Locked (confirmed via row menu displaying "Unlock Row") Text, Date, Contact List, and Checkbox columns cannot be modified. Dropdown List columns remain editable. Selecting a new dropdown value and…
