How to expand formula to other cells.

[Tricks420]

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

[JohnTopley]

in H3

=SUBSTITUTE(MIN(CHOOSECOLS(FILTER(Data!$D$2:$AB$18,(Data!$A$2:$A$18=$A3)),SEQUENCE(,COUNTIFS(Data!$D$1:$AB$1,H$1),MATCH(H$1,Data!$D$1:$AB$1,0))))&"-"&MAX(CHOOSECOLS(FILTER(Data!$D$2:$AB$18,(Data!$A$2:$A$18=$A3)),SEQUENCE(,COUNTIFS(Data!$D$1:$AB$1,H$1),MATCH(H$1,Data!$D$1:$AB$1,0)))),"0-0","Optional")

or

=LET(a,Data!$D$2:$AB$18,b,Data!$A$2:$A$18,c,Data!$D$1:$AB$1,SUBSTITUTE(MIN(CHOOSECOLS(FILTER(a,(b=$A3)),SEQUENCE(,COUNTIFS(c,R$1),MATCH(R$1,c,0))))&"-"&MAX(CHOOSECOLS(FILTER(a,(b=$A3)),SEQUENCE(,COUNTIFS(c,R$1),MATCH(R$1,c,0)))),"0-0","Optional"))

Copy to J, l etc

See attached

[Tricks420]

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

[JohnTopley]

Please attach your w/book showing the problems: cannot read the attachment.

[Tricks420]

Here you go

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

[JohnTopley]

I think formula should be

=IFERROR(IF(VLOOKUP($A3,Data!$A:$K,3,FALSE)=0,"No Pinch",VLOOKUP($A3,Data!$A:$K,3,FALSE)),"")

[josephteh]

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"))

[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?

[josephteh]

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)

[Tricks420]

It works, thank you @JohnTopley!