In the attached workbook, I would like to have a tab where I find errors using a True/False table (A5:B7), then I show only the rows with "TRUE" on the Error Log tab.
This is essentially replacing someone from having to go in and refilter each time. As soon as they fix the error, it should be removed from the Error Log listing.
I know I did something like this with an array but I can't remember how.
Thanks!
try this ARRAY formula...
A2=IFERROR(INDEX(Data!B:B,SMALL(IF(Data!$A$6:$A$7=TRUE,ROW(Data!$A$6:$A$7)),ROWS($A$1:A1))),"")
**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
Then copy down as needed.
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Hi, you can copy this down in your sheet "Error Log":
This is not an array function!
Please Login or Register to view this content.
Cheers
Erwin
Last edited by Eastw00d; 05-27-2020 at 12:37 AM. Reason: additional information
I started learning VBA because I was lazy...
Still developing.... being more lazy...
Actually, it is an array function, but because you are ysung AGGREGATE, you just dont need to use CSE to enterOriginally Posted by Eastw00d
Hi, you can copy this down in your sheet "Error Log":
This is not an array function!Please Login or Register to view this content.
Cheers
Erwin
Thank you for pointing this out, you are right; I will put that differently in the future
Cheers
Erwin
No problem
If you look at how I structured my INDEX, you will see I used full column ref. doing it that way does away with the need to subtract rows from the ROW() function...
-ROW(Data!$A$6)+1
Thanks, I think that's similar to the original formula I use way back when...although I forget how it works, but it works
Thanks. However, when I use it, it seems to be returning the TRUE cell.Hi, you can copy this down in your sheet "Error Log":
This is not an array function!Please Login or Register to view this content.
Cheers
Erwin
I think you need to adjust the 1st reference...
INDEX(Data!$A$6:$A$7
to col B instead...
INDEX(Data!$b$6:$b$7
Ford - quick question. When I applied the formula to my sheet, I made it reference a table. It worked well, except it seems to be ignoring the first row (after the headers). Any ideas?
Did you adjust the range to include that 1st row?
Hi, I was merely doing what was asked:If you change the formula into:< "TRUE" errors should show up here.
you will have the "B"-column as answer.Please Login or Register to view this content.
Cheers
Erwin
Another suggestion for you - if you just use col B, instead of A:B, you wont need to add the ,2 at the endHi, I was merely doing what was asked:
If you change the formula into:
you will have the "B"-column as answer.Please Login or Register to view this content.
Cheers
Erwin
=IFERROR(INDEX(Data!$b$6:$B$7,AGGREGATE(15,6,ROW(Data!$A$6:$A$7)-ROW(Data!$A$6)+1/(Data!$A$6:$A$7=TRUE),ROWS('Error Log'!$A$2:A2))),"")
And thank you both for the feedback, too - always appreciated
Great solutions! Thanks everyone.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks