I'm trying to construct a formula which which looks at a column of data and checks if a number of answers are found. Because this is a weekly report, the number of entries in this column will differ and so I need to look up the whole column. Also, both the column data and the references will be on two different tabs. I hope this is clear enough to help me!
I think we're going to need some more specific details.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
OK, I have knocked up a dummy spreadsheet, see attached.
I want the formula in C3, in Result Tab, to check column A in the Check Tab for the locations highlighted in red in the Reference Tab.
If there are any of these locations found, then the result should be 'BREACH', if none are found, then the result should be 'OK'.
I have already conditionally formatted cell C3 in Result as per the screenshot on the tab.
Cheers.
The attachment may help....
The codes in red on the reference tab do not have Conditional Formatting rules attached.
You can only detect the colour of a cell using VBA.
You're gonna need VBA to detect those red cells unless you can set them with Conditional Formatting.
Regards
Special-K
Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
The colour of the cells in the reference tab are only there as a guide as to which ones are ineligible, not to necessarily be used within the formula. Surely the cell references can be used to check?
Hi, can anyone help with this?
Hi,
Try this
With A helper Column & Name Manager
Punnam
Thanks, that works nearly perfeclty. The only problem is, is that because the real data that is going to be copied into he 'Check' tab is quite large and so I dont want to be adding in another column to include a Countif formula. Plus, as it may not just be me running this report, I want to make it fool proof and so it just checks the column for the particular codes and returns the answer. Can this be done? Thanks.
I tried but unable to get one , wait for more solutions
Punnam
Try this...
On the Reference sheet, in column B enter a X in the cells that you want to check:
Data Range
Then, on the Result sheet enter this array formula** in C3:
=IF(SUM(--ISNUMBER(MATCH(Check!A2:A10000,IF(Reference!B3:B25="x",Reference!A3:A25),0)))>0,"Breach","OK")
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Hi, thanks for this. It worked perfectly in my test report, but as soon as I tried transferring it into my real report (obviously changing the sheet names and cell reference etc), it wouldn't work.
The data that I am using as my check is grouped, would this make any difference to how this formula works?
Shouldn't, no.Originally Posted by Russ76
I can post a copy of the sample file you attached where the formula does what you asked for but I'm assuming that's the same file you tested the formula in?
I have a "thing" about downloading big files. I usually don't do it if the file is >50kb but I made an exception for your file (70kb).
I'm guessing that your real file is much bigger than 70kb in which case I don't want to download it!
Maybe someone else will be willing to look at the real file.
Hi Tony,
the real life file is much bigger but also it is a work report and so contains sensitive info, and so I can't/won't upload it.
It's really frustrating, as the solution that was offered worked really well in the test but for some reason won't in the real life report. I'll keep on working through it though as it might just be a glitch.
When you said it wouldn't work can you be more specific?
Did you get an incorrect result? An error? Something else?
Hi, I managed to get the formula working. It seems I was being an idiot and not entering the formula correctly ie Ctrl,Shift,Enter.
I'm now trying to get a Countifs formula working which would count all of the values in green from the 'reference' tab which are found in the 'check' tab.
I've played around a bit trying to get this to work but I keep on receiving a 'zero' result. Any offers?
Cheers, again!
If you put the criteria in a contiguous range like Reference!A3:A10...
Then, this formula entered on the Result sheet:
=SUMPRODUCT(COUNTIF(Check!A:A,Reference!A3:A10))
Hi, thanks Tony. I tried that formula but it only pulled through 3174 as a result when the figure should be 5701. I copy and paste your formula and so is there something I am not doing?
Based on the sample file in post #4 I also get a result of 3174.
This can be verified by using intermediate formulas and then getting the sum of the intermediate formulas.
On the Reference sheet:
Data Range
This formula entered in B3 and copied down to B10:
=COUNTIF(Check!A:A,A3)
The results of both formulas are the same.
It's quite a difference from 3174 and 5701. Maybe I didn't understand what needs to be counted?
For some reason, its not pulling through the figures for 'DEP'. In your table you have a figure of zero but I have done a manual check and found that there is 2527, which combined with the above equals 5701.
OK, found the problem.
In the sample file on the Reference sheet, cell A8 contains DEP but there's a trailing space character: DEP_.
Remove that space character and you'll get:
Data Range
Hey,
I hope you have changed the Cell Range also.
Reg,
CVishu
Which cell range?
the cell range is given as A3:A10 is that the whole range for your data
That's just the criteria range. The data range is Check!A:A.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks