Auto populating data from another sheet

kkeung84246

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

Paul Newcome

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

kkeung84246

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.  

Paul Newcome

What is your original formula, and how are you building the new formula to reference another column? What is automatically updating?