Cross-sheet formula

steve50951

I would like for sheet to auto populate certain fields based on entering in a "property name"

When I enter a property name, I would like for it to access the fields from another sheet that has that property name, and input values for that property.

would I use a =vlookup? 

Thanks for you help!

SGF

Paul Newcome

A VLOOKUP function would work, but I prefer the flexibility of an INDEX/MATCH. The way that is set up is pretty straightforward. It would look something like this...

 

=INDEX({Sheet 1 Range 1}, MATCH(Property@row, {Sheet 1 Range 2}, 0))

.

{Sheet 1 Range 1}: Cross sheet reference where you would select the entire column on the first sheet that holds the information you are wanting to display.

 

Property@row: The cell reference to the column on your second sheet that houses the property name.

 

{Sheet 1 Range 2}: Cross sheet reference where you would select the entire column that holds the property names.

.

From there you would just update the first range to reference the appropriate column in your fist sheet for the data you are wanting to display ({Sheet 1 Range 1}). Everything else would stay the same.

There are a few other ways to increase flexibility, but this would be a pretty straightforward approach that will allow you to rearrange the columns and sort the rows on both sheets without having to worry about breaking anything like you would with a VLOOKUP.