Formula Assistance with Linking Sheets

Asenath

Hi!

I have created a sheet that uses a form for requests when my company is wanting a physician to travel. This includes what center/state they're traveling to, their home center/state, the district manager that oversees each center, etc. When a physician is selected I would like for the home center, home state, home center division, home center division manager to auto populate based on the physician name. I have a physician roster for this info to get pulled from but for some reason my formula is not working correct. For ex. when I type in "Orlando" it puts the state is "AL" but when I put in "Tampa" is correctly puts "FL". I have already checked the roster to make sure there is no errors so it seems to be something is off in the formula. If anyone has any tips for the formulas or what might be off please lmk

OHS

Hi,

I am not an expert on this but perhaps Index or Index Match could accomplish what you are trying to do. Hopefully this will be helpful:

INDEX and MATCH across two sheets: a detailed explanation

Brett Wyrick

Nov 8, 2021

Hello everyone, would love some feedback on this tutorial.

I was answering a user's question regarding INDEX and MATCH (thread post here), and thought it would be beneficial to share my example to the greater Smartsheet community of how to easily utilize INDEX and MATCH functions to reference cells from another sheet by using a shared "identifier" value.

I couldn't find any sources of a true step-by-step tutorial of how to create sheet references to a second sheet range, then show how to utilize INDEX/MATCH to get the info from those ranges.

This tutorial includes a step-by-step example of how to create one sheet as a "data source" sheet, and a second sheet that's used for a more "public" sort of view. The idea here is for the data to have a "home" on the source sheet and a select set of columns available on the other sheet (useful for sharing when you don't want to share all the data from a sheet).

----

Documentation so you can get savvy:

Here's the documentation from Smartsheet for both Index and Match.

• INDEX: https://help.smartsheet.com/function/index
• MATCH: https://help.smartsheet.com/function/match

Below, I'll try and explain a layman's terms way to do INDEX MATCH - I'd recommend using INDEX/MATCH functions rather than VLOOKUP because INDEX/MATCH functions are more helpful if your data moves around (i.e., if one row gets moved, VLOOKUP can "break").

For your use case, you'll be utilizing what Smartsheet refers to as "Cross-sheet references". You can read about Cross Sheet Formulas (and there's a video) here: https://help.smartsheet.com/learning-track/smartsheet-advanced/cross-sheet-formulas

----

Due to limits on characters for posting, I'll add my solution as several responses to this discussion post.

---

View Post