So I understand you want it to check BI11 and BJ11 in addition to BH11.What I need to understand is what do you want to happen if BH11 is < 9, and BI11 is <9.Do you want it to return multiple values and add them all up?If so that can be done by nesting the IF statement within a SUM, and calling multiple IF statements one for each column reference.If you can clarify what your after I can build something for you, but off the top of my head its looking like=sum(IF(COUNTIF('Work Sheet 1'!$AB$46:$AD$55,'Work Sheet 2'!BM2)=BH11<9,"",3),IF(COUNTIF('Work Sheet 1'!$AB$46:$AD$55,'Work Sheet 2'!BM2)=BI11<9,"",2),IF(COUNTIF('Work Sheet 1'!$AB$46:$AD$55,'Work Sheet 2'!BM2)=BJ11<9,"",1))
Hi - thanks for picking this up. The cell from which I am writing the formula is conditionally formatted to different icons depending on what is in either BH11, BI11, or BJ11. There will only ever be one value in any one of those three cells; a number less than 9. If there is a number in BH11 then the conditional formatting will return an icon based on the value "3". Likewise, BI11 returns an icon for "2", and BJ11 for "1". So I need the formula to look at all three cells but only pay attention to the one in which there is a value.
Ok thats easier then
Without having your excel document its hard to test, but all we are doing is, adding a component into the IF false statement..
IF(COUNTIF('Work Sheet 1'!$AB$46:$AD$55,'Work Sheet 2'!BM2)=BH11<9,IF(COUNTIF('Work Sheet 1'!$AB$46:$AD$55,'Work Sheet 2'!BM2)=BI11<9,IF(COUNTIF('Work Sheet 1'!$AB$46:$AD$55,'Work Sheet 2'!BM2)=BJ11<9,"",1),2),3)
Not quite. It's returning an icon for "3" no matter which cell (BH11, BI11, BJ11) I enter a value less than 9 - or leve them all blank. I'm not sure it's very relevant but if there is a value in BH11 then the icon is a red diamond, a value in BI11 should return an orange triangle, or a green spot for BJ11. With the formula above I get a red diamond in all circumstances. I reversed your last three digits and produced the result of a green spot - but again in all circumstances rather than specifically relating to BJ11.
Ok, can we do a remote session?
If you goto https://broker.gotoassist.com/h/ao?Question=DR431-583-488
If you cant show me the spreadsheet, can you confirm Cell Work Sheet 2!BM2 is set to 1
If not what value, and why
my system at work may not allow it - sorry for taking so long to get back to you, the tab locked up. I finally twigged to c'n'p the url into a new tab. d'oh!
In pulling this apart further I also dont understand why you have a condition in the If statement of =B13<9
As you cant have something equal B13 which is also <9
Cell Work Sheet 2!BM2 is a system application - it's what the formula is looking for in the range from Work Sheet 1.
Right thats now how the formula is setup from what I can tell.It will only ever count the value if the cell value in BM2 is equal to 1 (True)
Have you tested each part of the formula, as I have tried to re-create things at my end but without understand the raw data its a little confusing
So, Work Sheet 1 is a data entry sheet and there is a pick list in a range (AB46:AD55) for system applications to be selected in. If the application listed on Work Sheet 2 in cell BM2 is within the range from Work Sheet 1 then the formula should look at the range in Work Sheet 2 - BH11:BJ11 to see what icon it should reflect.
Ok so your looking for text
Initally - in the range on WS1, then a number in the range on WS2. Sorry I can't share it with you :-(
OK I can probably model something on that, what about the = BM2<9
Sorry that should have been BH11<0
The <9 is reflecting how soon a system is needed to be available again if we lose it. So BH11 = systems needed back up and running on the same working day. The <9 is to caputre whatever is entered in terms of hours. Generally it's a number between 1-5 but I wanted <9 to cater to ludites. BI11 refers to system applications needed back within 1-5 working days and BJ11 for system applications not needed for 1-4 weeks. So depending how quickly something is needed back there will be one digit (<9) in one cell in the range BH11:BI11.
Ok, so lets focus on BH11
Hence the traffic light system for the icons that are conditionally formatted to appear depending on the value.
Up to now, I've only shown one result in the icon cell using this formula: =IF(COUNTIF('Recovery Needs'!$AB$46:$AD$55,'Critical Apps Map NO EDIT'!BO2)=0,"",0)
So If I have a value in BM2 found in the pick list (AB:AD) it should return 1, else it should return 0Then we are saying if the above resolves to 1, AND BH11 is < 9, we want to return 3, else ""Is this correct
It doesn't refer to the range on WS2 at all and shows a black spot. I want to elevate the return so that I can automate an ITDR list for prioritised recovery of our systems.
Yes! That's it! Can we do it?
cross thread. i hope you get my first sentence in this reply refers to the last sentence before your last reply?
Yes, need to start again from scratch though, this is the hard thing about formula you need to get the logic. Your issue is your trying to create a second condition within the condition for the Count
Give me a few mins
Lets get it working correctly with BH11 then we can easily expand it
I wish I knew so much. I only know the result I want. Gosh it's going to be beautiful when it's working :-)
Ive had formula with 5 or 6 lines and all sorts, you just prey it never goes wrong :-)
=IF(COUNTIF('Work Sheet 1'!$AB$46:$AD$55,'Work Sheet 2'!BM2)=1,IF(BH11<9,TRUE,"Not <9"),"List Error")
If you get True we just need to replace TRUE with 3
Sorry for the delay - I'll try now.
It's returning the red diamond icon but in all circumstances - whether there's something in BH11 or not :-(
Can we remove conidtional formating
As I need to see the error
Or just paste teh formula in another part of the worksheet
I need to see if we are getting a List Error or <9 error
Ok, I'll do that now.
The result is "3" no matter what is or isn't entered in BH11
The last part of the formula does say "List Error")
Let me go and check my instance
It cant return 3, or did you change TRUE to = 3=IF(COUNTIF('Work Sheet 1'!$AB$46:$AD$55,'Work Sheet 2'!BM2)=1,IF(BH11<9,TRUE,"Not <9"),"List Error")
Sorry I did change True to 3
I'll change that back, sorry.
Its ok, just making sure it wasnt an old formula
So its now showing True
Also, I just realised that 'nothing' is = <9 so perhaps it should be >0 ?
Yes I fixed that as part of me adding the other columns
I'm getting True in the cell.
The weird thing is if you had nothing entered (null) it should show list error, as the count if should fail
I couldn't get it to work with ">" so I left it as "<".
Right so if you type XXXX or something in BH11 its still showing True
No, that shows "not true". However having the cell blank still returns "ture"
PS: whether we get to the result, your service has been excellent. Thanks very much for everthing so far!
Ok I can replicate it this end, will have a play shouldnt be more than a few mins
=IF(COUNTIF(Work Sheet 1!$AB$46:$AB$55,Work Sheet 2!BM2)=1,IF(AND(BH11>=1,BH11<=9),TRUE,"Not <9"),"List Error")
Should there be single speech marks around the work sheet names?
Yes im using office 2013, so probably removed them
So, so, so close. I'm getting true and not true. I changed TRUE in the formula for 3 and applied the conditional formatting and it works! ... except that it shows 'not true' if there isn't anything in BH11. Perhaps that's not a problem when we extend it to to BI11 & BJ11?
=IF(COUNTIF('Work Sheet 1'!$AB$46:$AD$55,'Work Sheet 2'!BM2)=1,IF(AND(BH11>=1,BH11<9),3,IF(AND(BI11>=1,BI11<9),2,IF(AND(BJ11>=1,BJ11<9),1,"Not <9"))), "List Error")IF(AND(BH11>=1,BH11<=9),TRUE,"Not <9")
In that there will always be something in one of the cells.
Try the above
So this works by checking BH11, if its invalid (greater than 9 or empty), it checks BI11, and if thats invalid it checks BJ11, and if that is invalid it returns "Not<9"
It works! :-)
I didn't use the second line of the formula though so I'm still getting 'not true' if there isn't anything in any of the three cells. How do I include the last part of the formula?
Wow, this is thrilling!
So you just need to change "not <9" and "List Error" to something the end users can understand or maybe just to "", and you are sorted
I'll try. Please don't leave me just yet.
The formula you need is =IF(COUNTIF('Work Sheet 1'!$AB$46:$AD$55,'Work Sheet 2'!BM2)=1,IF(AND(BH11>=1,BH11<9),3,IF(AND(BI11>=1,BI11<9),2,IF(AND(BJ11>=1,BJ11<9),1,""))), "")
I am SOOOOO greatful! Thank you a million times over! This is a fabulous breakthrough for my spreadsheet.
Terrible typo. I mean.
I am SOOOOO grateful! Thank you a million times over! This is a fabulous breakthrough for my spreadsheet.
Not a problem, the trick is to break down the formula into smaller components, eg the CountIF, and prove it works, as your main issue was the =BH11<9 part
I would appreciate if you can rate the level of service received today, should you need anything else feel free to contact me via http://www.justanswer.co.uk/computer/expert-james-i/?rpt=3800
Wonderful, wonderful. I have learned a lot. You are released. Please help me again if I reach out. I'll be happy to pay more next time. Was dubious as a first timer and ratcheted the system down so that I wasn't over committed. I hope you understand.
Will do with bells on! :-)
If you use the link above to ask the question it will come to me directly
I've just saved it for exactly that purpose. Have a great day. You've earned a break, that's for sure!