Automatically copy cell based on 2 criteria in another sheet

maineL

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

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

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!!!!!😁

Paul Newcome

Happy to help. 👍️

maineL

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

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

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

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

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

Paul Newcome

Ok. Try this instead then:

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

maineL

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

Happy to help. 👍️