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)
So in as few words as possible. This sheet will be used as a template and gets created weekly. I want to extract; Machine Line #, Downtime =>4, and cause data cells into a table or pivot chart that can be used to show downtime by machine and reason.
See my example charts.
One thing I did was highlight the data for the week then apply a filter of =>4, then copied that data to make the chart you can see. I am thinking there is a better way.
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)
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
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.