How to expand formula to other cells.

Hello,

I am trying to create a sheet that tracks when we hit our targets and when we miss them. So far I have been able to get the sheet to work for the first name on the list "Black Jelly". This is how it works:

1) I select from the drop down list in column A.
2) When I do, "1st Touch Target", "2nd Touch Target", "3rd Touch Target", "4th Touch Target", "5th Touch Target", and "Final Defol Target". are populated automatically with the numbers from the Data tab.
3) When "Black Jelly" is selected, it looks at all the "1st touch" numbers from the Data tab in cells D2-G2.
4) It then populates the numbers in cell H3 as a range using the highest and lowest numbers "5-7".
5) It does the same for 2nd, 3rd, 4th, 5th, and Final touches.
6) If there is no number populated in the cells on the Data tab, it populates "Optional" instead.


I need help with:

1) I would like to continue these formulas through all the cells in their appropriate columns.
2) I would like the info to change depending on the Cultivar picked in column A.

Here is my file: WIP - Crop Work Targets & Misses.xlsx

Try, in F3, copy down and across:
=IFERROR(AGGREGATE(15,6,Data!$A$1:$AB$18/((Data!$A$1:$AB$18<>"")*(Data!$A$1:$A$18=$A3)*(Data!$A$1:$AB$1=F$1)),1)&" - "&AGGREGATE(14,6,Data!$A$1:$AB$18/((Data!$A$1:$AB$18<>"")*(Data!$A$1:$A$18=$A3)*(Data!$A$1:$AB$1=F$1)),1),IF(F$1="Pinch","No Pinch","Optional"))

Hi John,

Thank you, your file seems to work well. I added an IFERROR function to the formulas to remove the errors when cells are empty.

However I am getting another error, when I make all the names the same. Under "Pinch Target" some of them say "No pinch" while others sale "0-4". I can't seem to figure out why.

Attachment 881091

Here you go

WIP - Crop Work Targets & Misses (JT).xlsx

Hi Joseph,

It appears to work, however all the empty cells have "No Pinch" auto-populated into them. Is there anyway to remove that so they are only displayed when data is entered?

Awesome, that fixed it. However it is now doing it to the other Target columns. They say "Optional".

I think I am just getting confused with the 2 different answers.

It works, thank you @JohnTopley!

Originally Posted by Tricks420

Hi Joseph,

It appears to work, however all the empty cells have "No Pinch" auto-populated into them. Is there anyway to remove that so they are only displayed when data is entered?

Just add in front, IF(A3="","",formula)