I am trying to write a formula that returns data from a different sheet in the same workbook. I currently have a nested formula of 2 arguments and another single argument and am wanting to combine them. As is, they are working fine.
Book 1 is my source report. Book 2 is the report I'm creating with these formulas.
Book 1 column B has (in this example) the first name and the total. Book 1 column C has the last names. The current formulas return the greyed out names in Column A as long as the text in Book 1 column B <> "Total." What I'm needing to do is move those greyed names into the same column as the last names. So I need to somehow combine the 2 formulas. Basically what I'm wanting to say in $B is:
If Book 1 B1 is not blank but not "Total" then return B1 -OR- if Book 1 C1 is not blank then return C1. If Book 1 B1 = "Total" then B1 is blank.
Nesting this nested and single formula creates too many arguments. I also tried:
=IF(OR(Book 1!C3)<>"",Book 1!C3,""),IF(AND((Book 1!B3)<>"",LEFT(Book 1!B3,5)<>"Total"),Book 1!B3,""))
(If C isn't blank, return that or if B isn't blank but isn't "Total", return that. Else leave blank) Excel says there's a problem with the formula. I don't fully disagree :)
I am probably way over thinking this.