How to extract cell information based on another cell entry

I have an excel sheet with the following layout: My goal is to find each occurrence of a machine number that has a downtime equal to or greater than 4 hours and put that info Plus the reason into a s...

Formulas & Functions

Show and Tell

Matt Mickle
Apr 02, 2018
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)

Test_For_Data_Extraction v1.xlsx45 KB

Benjamin Davis

Copper Contributor

Apr 03, 2018

Thank you so much for your continued assistance. This looks great so now to try and understand how each portion of the array formula works and this is the first I ever heard of a myPivotRange. So I shall try and see how it all works. But I believe you have solved the immediate need. Thank you again.

Matt Mickle

Bronze Contributor

Apr 03, 2018

Glad to help! Just be aware that myPivotRange isn't a standard Range. It's a named range that I created to fit your needs. If you want to know more about named ranges check out one of these links:

https://exceljet.net/lessons/how-to-create-a-named-range

http://www.contextures.com/xlNames01.html

Forum Discussion

How to extract cell information based on another cell entry