I have been wracking my head over nested if functions. I seem to get the opposite of what is expected with a false answer being given when it should be a true answer! I have 4 possible responses between 3 columns that are yes or no. If the first column is no then the response should be no, if it is yes and the other two columns are no it should be "Yes-Review/Negotiation in Process." If the first is no and the second is yes and the third is no the response should be "CSTIP Approved." If all are yes the response should be "DGLHA Approved." I changed the "yes" and "no" to 1 and zero so I could use the total for a row to apply the category.
0=No; 1= Yes-Review/Negotiation in Process; 2= CSTIP Approved; 3= DGLHA Approved
I pulled a table from another table to get the 22 Budget Status Snapshot Flow-thru. From that table I need to create a table my supervisor can view in Card View based on the 4 categories.
The formula before added the "total" column:
=IF(AND((VLOOKUP(Grant@row, {22BudStatusFlow}, 6, false)= 1), (VLOOKUP(Grant@row, {22BudStatusFlow}, 5, false) = 1), (VLOOKUP(Grant@row, {22BudStatusFlow}, 4, false) = 1)), "DGLHA Approved", IF(AND((VLOOKUP(Grant@row, {22BudStatusFlow}, 6, false)= 1), (VLOOKUP(Grant@row, {22BudStatusFlow}, 5, false) = 1)), "CSTIP Approved", IF((VLOOKUP(Grant@row, {22BudStatusFlow}, 6, false)= 1), "No", "Yes-Review/Negotiation", "No")))
After the total column:
=IF((VLOOKUP(Grant@row, {22BudStatusFlow}, 7, false)= 3), "DGLHA Approved", IF((VLOOKUP(Grant@row, {22BudStatusFlow}, 7, false)= 2), "CSTIP Approved", IF((VLOOKUP(Grant@row, {22BudStatusFlow}, 7, false)= 1), "Yes-Review/Negotiation", "No")))
This is the flow-thru table
This is the final table:
This would be a piece of cake in Excel, but it isn't working worth anything in Smartsheet! This should be easy!
