Adding an additional Condition to a working Formula
Hi Folks
I have this formula:
Formula:
=IFERROR(INDEX('[UKIRE EACHWAY RESULTS.xlsb]Data'!$E$2:$E$200000,INDEX(MATCH(A2&Y2,'[UKIRE EACHWAY RESULTS.xlsb]Data'!$C$2:$C$200000&'[UKIRE EACHWAY RESULTS.xlsb]Data'!$D$2:$D$200000,0),)),0)
and wish to add this pre-condition:
is TRUE then the above formula continues, else to return 0Formula:IF('[UKIRE EACHWAY RESULTS.xlsb]Data'!$B:$B,"TO BE PLACED"
Cannot get it right-
Cheers
hammer
If you have found solving my problem/s to be an interesting and educational exercise then how about Repping me up?
No idea why I need Rep, other than feeling left out....
I'm not sure what your pre-condition relates to. Are you saying that you only want to consider rows where the cells in column B contain the phrase "TO BE PLACED"?
Pete
That is correct thanks Pete-
Thanks
Hammer
Okay, try it like this:
=IFERROR(INDEX('[UKIRE EACHWAY RESULTS.xlsb]Data'!$E$2:$E$200000,INDEX(MATCH(A2&Y2&"TO BE PLACED",'[UKIRE EACHWAY RESULTS.xlsb]Data'!$C$2:$C$200000&'[UKIRE EACHWAY RESULTS.xlsb]Data'!$D$2:$D$200000&'[UKIRE EACHWAY RESULTS.xlsb]Data'!$B$2:$B$200000,0),)),0)
(changes shown in red). Note that this is an array formula, so you must commit it using the key combination of Ctrl+Shift+Enter instead of the usual < Enter >.
The ranges you have used are quite large, so this is likely to be quite sluggish, especially as it refers to an external workbook - it would help if you reduced the 200000 (if possible).
Hope this helps.
Pete
Hey Pete
Works perfectly!
No problems with sluggishness as each day's data is copied over and pasted back in as values-
Many thanks for your time and expertise once again!
Cheers
hammer
Glad to help - thanks for the rep.
Pete
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks