Additional criteria in a forumula required

HI Guys
I’m hoping you can help me with some suggestions
In the attached sheet, there is a formula copied down in column S that looks at various criteria across columns K, N and O. It looks to see if there is a date in column K, then if a date is present in columns N and O before returning the desired result (ie. the BG reference)
What I need in this formula is also (as well as the above) to include the criteria stating if the date in column T (or K however I was getting a circular reference) is greater than the current day’s date, then its OK to still show the BG reference. If the date is less than today’s date, then the respective cell in Column S will return a blank.

I tried modifying the formula is column S to read but am getting a circular reference error :-
=IF(K2="","",IF(N2<>"","",IF(O2<>"","",if(t2<$z$1,””,LEFT(B2,8))))

For example, I have highlighted the items in question, in Column V, in orange. For example , row 5, cells S5 to Y5 would be blank as the date is less than today’s date. As all these cells bounce off a value being present in Column S, they would automatically be empty if the formula in S5 contained the ‘date’ if then criteria.

Any help would be greatly appreciated
J
ExpoJoJoReport---Test---Copy.xls
Jase AlexanderCompliance ManagerAsked:
Who is Participating?
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You are having issue because you have too many dependent columns.
See if the following formula works for you.

In S2
=IFERROR(IF(OR(K2="",N2<>"",O2<>"",IF(LEFT(H2,2)="UK",K2+4,K2+8)<TODAY()),"",LEFT(B2,8)),"")

Open in new window

1
 
als315Commented:
Try this sample (you should move all IF from other cells to S)
ExpoJoJoReport---Test---Copy.xls
0
 
Jase AlexanderCompliance ManagerAuthor Commented:
Superb! It worked perfect.

Thank you so much for the help - I appreciate it

J
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome. Glad I could help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.