Automatically copy cell based on 2 criteria in another sheet

Options
2»

Answers

  • maineL
    maineL ✭✭✭
    Options

    You are the Smartsheet Master! Wow, mind blown! Thank you so much. I do have one more issue...Of course!!!

    So when the course is selected, I have it set up to pull data from my Details sheet to copy information from that respective row. So if a course is Sta (state) or End (endowed), the respective account should be Number (I81/191) or ANumber (A81/2). For some reason it's pulling the wrong information from the details page, which has the correct data:

    Active TAship

    Details Sheet - specifically the Stipend Acct column

    The formula I'm using is: =INDEX({Details StipendAcct}, MATCH(Course@row, ({Details Course})))

    But as you can see in the Active Sheet above, the Stipend Account works in some cases (rows 1, 3 and 4), but row 2 (which is STA and should be 191/192) has the wrong account, but it's accurate in the Details sheet. So I'm unsure what I did wrong. I was trying to use the formula as a column formula.

    Any ideas? Thank you!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Try using 0 (zero) in the final portion of the MATCH function. This specifies an exact match whereas the default is just a "close match" (which often times "close" is the wrong word for it).

    =INDEX({Details StipendAcct}, MATCH(Course@row, {Details Course}, 0))

  • maineL
    maineL ✭✭✭
    Options

    You did it!!!!!! I cannot thank you enough!!! I think I'm set with all of my sheets now. I'm sure I'll bump into something else! THANK YOU SO MUCH!!!!!😁

  • maineL
    maineL ✭✭✭
    Options

    I lied...one more. I would like when the Field contains "Engineering", that the Tuition Rate changes to $14,750 rather than the standard $10,400. I've been entering it manually, but it would be nice if I could get it automatic. I currently have this formula:

    =INDEX(Eligibility Field}, match(Field@row ="Engineering", (Details HigherRate})

    Active Sheet

    I'm sure I'm missing something with the formula. I currently have conditional formatting set up to change colors for any of those cells at the higher rate, but I'm not sure this is even an option.

    Thank you!!!

    Lori

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Double check the syntax. The syntax should match your last INDEX/MATCH.


    Your last one:

    =INDEX({Details StipendAcct}, MATCH(Course@row, {Details Course}, 0))


    Your newest one:

    =INDEX(Eligibility Field}, match(Field@row ="Engineering", (Details HigherRate})


    Of course range and column NAMES may be different, but the structure of the formula should be the same (including the {curly brackets} wrapped around cross sheet references).

  • maineL
    maineL ✭✭✭
    Options

    Ok, I've tried this one: =INDEX({Details Tuition}, MATCH(Field@row = "Engineering", {Details HigherRate}, 0))

    But I'm getting No Match even when Engineering is in the field. Many don't have just Engineering. Some have College of or Biomedical Engineering, etc.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    The syntax is still off.

    =INDEX({Details Tuition}, MATCH(Field@row, {Details HigherRate}, 0))


    But taking a closer look, I see what is going on. Do you have a separate table that has the different rates in it? Or is it just anything that has the word "Engineering" in it gets $14,750 and everything else gets $10,400?

  • maineL
    maineL ✭✭✭
    Options

    Anything with the word Engineering in it should get the $14,750 only.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    Ok. Try this instead then:

    =IF(CONTAINS("Engineering", Field@row), 14750, 10400)

  • maineL
    maineL ✭✭✭
    Options

    You have completed this Smartsheet for me!!! You are TRULY AMAZING! Thank you so much for all of your help!

    Best wishes,

    Lori

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!