Hi guys!,
Seems like a simple formula but has me stumped basicaly if a cell text equals "away" I want the formula to go to the next cell within the group and use that unless that is also displaying "away"
Hi guys!,
Seems like a simple formula but has me stumped basicaly if a cell text equals "away" I want the formula to go to the next cell within the group and use that unless that is also displaying "away"
Last edited by mr63249; 11-27-2008 at 01:19 AM.
HI
Excel always evaluates the conditions in order, so if the first condition is true it will always display irrespective of the validity of the second.
If you try somethng like thisit should evaluate correctly.Please Login or Register to view this content.
Regards
Jeff
Thankyou! now i just have to incorporate it with
=IF(L6="Yasmina","Kendra",IF(L6="Kendra","Lauren",IF(L6="Lauren","Terence",IF(L6="Terence","Alycia",IF(L6="Alycia","Terence")))))
Tried this;
=IF(AND(B7="AWAY",B8="away"),A9,IF(B7="AWAY",A8,IF(L7="Yasmina","Kendra",IF(L7="Kendra","Lauren",IF(L7="Lauren","Terence",IF(L7="Terence","Alycia",IF(L7="Alycia","Terence")))))))
But it doesnt ignore and move on to next person when i selected them away
Would a DCOUNT formula be better? Not that I know how to use that either :D
If B3:B21 contain "away" (or not), then the first cell in A3:A21 next to a cell that isn't "away" is
=index(A3:A21, match(true, B3:B21 <> "away", 0))
This is an array formula, meaning it MUST be confirmed with Ctrl+Shift+Enter, not just Enter. (With the cursor in the formula bar, hold down Ctrl and Shift, then press Enter.) You'll know you did it correctly if curly braces appear around the formula in the Formula Bar; you cannot type in the braces directly.
mr63249, please take a few minutes to read the Forum Rules about thread titles before starting your next thread.
Last edited by shg; 11-23-2008 at 01:16 PM.
Entia non sunt multiplicanda sine necessitate
Hi
Given the number of variables involved it may probably be a better option to use VBA to determine the desired outcome.
You could link the code to a button and it should probably be a lot more flexible too.
If you would like to look at this let me know and I will see what I can do.
regards
Jeff
[QUOTE=shg;2001775]If B3:B21 contain "away" (or not), then the first cell in A3:A21 next to a cell that isn't "away" is
=index(A3:A21, match(true, B3:B21 <> "away", 0))
This is an array formula, meaning it MUST be confirmed with Ctrl+Shift+Enter, not just Enter. (With the cursor in the formula bar, hold down Ctrl and Shift, then press Enter.) You'll know you did it correctly if curly braces appear around the formula in the Formula Bar; you cannot type in the braces directly.
Thanks I have tried that formula and It works, but I was wondering can I include special condiotions eg so if l6 =yasmina then l7 would equal "kendra" if not away then go down the list to the next person so the one person isnt named twice on the same day as is the current problem, and if kelly away then then it would go back to only the people in that group a5:a10
Last edited by mr63249; 11-27-2008 at 01:19 AM.
I can't tell exactly what you're trying to do ...
Last edited by mr63249; 11-27-2008 at 01:19 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks