Help Finding the correct formula to gather a piece of data

Kshelt

Hello!

I am new to SmartSheet and am trying to work on an impact heat map for various projects across an organization.

In one column I have the project and its subtasks, then stakeholder group, the month of impact, and the degree of change (high, medium, low). I want to link that spreadsheet to another that will just shows the degree of change (in color) in the correct spot.

I was thinking I would need an index function or an if/then but I am not sure.

Main Sheet:

The sheet I need it to feed (colors are there as a sample of how it should look in the end):

I want it to pull the coloring from the first sheet into the second sheet in the correct place (month, project, stakeholder)

I don't think it will solely pull the color and understand I may need to assign a value (0,1,3,5) and then conditionally format it to pop out the colors.

Michael Drayton

Hello Kshelt; Please let me know if this works. I think this answers your question, but I don't if this is what you had in mind at the end of the day...good luck.

Main Sheet: Project Details

Column 1: Project and Subtasks

Column 2: Stakeholder Group

Column 3: Month of Impact

Column 4: Degree of Change (High, Medium, Low)

Secondary Sheet: Visual Impact Matrix

Step 1: Cell Linking

In Smartsheet, you can use Cell Linking to connect data between different sheets. This feature allows you to maintain a level of consistency and synchronization across multiple sheets.

1. Navigate to the Destination Cell: Where you want to display the data in the secondary sheet.
2. Link to Another Cell:
   • Right-click on the destination cell.
   • Choose "Link to Cell" from the menu.
   • Navigate to your Main Sheet and select the cell with the "Degree of Change" data.
   • Click "Link".

Step 2: Conditional Formatting

1. Highlight the Cell(s): Where you linked the "Degree of Change" data.
2. Conditional Formatting:
   • Navigate to the "Format" tab.
   • Click on "Conditional Formatting".
   • Click on "New Rule".
3. Define the Rule:
   • For High: Choose a condition where if the cell contains "High", then it will be formatted with a red background.
   • For Medium: Define a rule with a condition to format cells containing "Medium" with a yellow background.
   • For Low: Define a rule to format cells containing "Low" with a green background.
4. Save and Apply the rules to your designated range of cells or the entire column, as per your requirement.

Step 3: Ensure Accurate Data Transfer

• Manually check a few linked cells to ensure data is pulling through correctly.
• Note that if you make a change in the Main Sheet, it will reflect in the Secondary Sheet due to the cell linking.

Additional Notes:

• Remember that changes in the Secondary Sheet (where data is linked) will not be reflected back in the Main Sheet.
• Ensure your stakeholders have the correct access and permissions for both sheets if they need to view or edit the data.
• For larger or more complex datasets, consider utilizing Smartsheet's integration capabilities or API for more advanced data management and automation.

Again, you may need to adjust based on the specifics of your project and data, but this should give you a basic framework to get started. If you have additional details or need further customization, feel free to provide more info!

regards,

Michael