Linking cells that are dependent

edited 12/09/19 in Smartsheet Basics

This is going to be a tough one to explain, and I'm not sure if it's possible. That said...:

Endstate: For a finance-based sheet ("Sheet 1"), I want one of the values from a user's form (Amount USD) to populate in another sheet (Sheet 2").

Problem: There is a debiting manager (losing money) and crediting manager (gaining funds) along with the dollar amount for Sheet 1. Is there a formula string (maybe an IF THEN) that can check for a name in Column 1, Sheet 1 and Column 2, Sheet 1 - then populate the dollar amount in Sheet 2 based on the name in Column 1 or Column 2?

I'm not explaining this well, but anyone who can link up to help me will have earned a lunch (liquid or otherwise).


    SYSPK ✭✭✭✭✭✭
    edited 05/20/19

    Sounds like INDEX/MATCH would be useful (google to find articles and video) but I believe the manager names that are to be matching must be spelled the same.

    Maybe there is another formula like FIND instead of MATCH that can give approximate values? Maybe someone else can chime in here.


    Edit to add: INDEX/MATCH works differently in Smartsheet than it does Excel, so make sure you're looking up a Smartsheet resource.

  • L_123
    L_123 ✭✭✭✭✭✭

    =index({dolla bill #1},match(name,{name 1},0))+index({dos dolla bill},match(name,{name 2},0))


    Provided the names are spelled correctly in every occurance something like this should work.