Forum Discussion
How to extract cell information based on another cell entry
Ben-
Try this file. I updated the array formula and added a dynamic named range that adjusts based on the array formula. It looks at rows W2:W41 (i.e. 40 cells) and adjusted the range based on the difference:
myPivotRange
=OFFSET('Weekly Template'!$W$2,0,0,40-COUNTBLANK('Weekly Template'!$W$2:$W$41),3)
Try using this array formula:
=IFERROR(INDEX(A$2:A$8,SMALL(IF($C$2:$C$8>=4,ROW(A$2:A$8)-ROW(B$2)+1),ROWS(B$2:B2))),"")
Confirm with CTRL + SHIFT + ENTER and drag over to the right and then down
I've also attached an example file:
Thank you soooo! much Mike. It works great in the example. Now the problem is I am on MacOS High Sierra 10.13.3 with Excel for Mac 2016 and according to Office Support
+ SHIFT + RETURN
or
CONTROL + SHIFT + RETURN
Using this key combination should allow me to enter as an array formula, however, I have a no joy condition again, not to mention dragging to the right and down. Andy ideas?
Try Ctrl + Shift + Return
When you enter the formula correctly "curly braces" will show around the formula.
{=IFERROR(INDEX(A$2:A$8,SMALL(IF($C$2:$C$8>=4,ROW(A$2:A$8)-ROW(B$2)+1),ROWS(B$2:B2))),"")}
I am stuck here: this is the concept and formula thus far.
I have a table as structured in the screenshot. I want to extract the Line #, Downtime =>4, and Reason to a separate table to create a chart by line number. Using this formula {=IFERROR(INDEX(Line,SMALL(IF(Downtime=>E4,ROW(Line)-MIN(ROW(Reason))+1),ROWS($E$5:E5))),"")}. Just can't quite make it work. Could you offer suggestions
The difference in the first suggestion maybe because I did not represent my true worksheet layout.
Your screenshot shows up really small and is blurry when I enlarge it. Is there anyway you can provide an example workbook with the same setup? Please provide non-sensitive data.
