I have multiple formulas that are reading from one another and I currently have them setup to read all the data in a set column and I believe that is causing the sheet to run out of resources while computing. The max location and min location formulas are the ones I think a UDF could help. I was thinking if I could tell it what I want to evaluate and have it evaluate the data to the last row in the data tab that should save a significant amount of time computing and also should always change if the amount of data in the tab changes. I tried to figure out how to dynamically pick the last row in a dataset within excel formulas without going into VBA but I am thinking now that it might make sense to create a user defined formula but I have never messed around with one and it seems to be a bit more involved than my skillset allows for.

I have a summary sheet which is pulling information from a data tab depending on specific criteria

see table below that is in my summary sheet Assume "CN" is in cell A1 and "min location" is in cell F1

CN | sub category | max | min | max location | min location |

CN 101 | p | 7 | 0.4 | CN 101_3 | CN 101_2 |

CN 101_1_T | p | 4 | 1 | CN 101_1 | CN 101_1 |

CN 101_2_T | p | 0.6 | 0.4 | CN 101_2 | CN 101_2 |

CN 101_3_T | p | 7 | 3 | CN 101_3 | CN 101_3 |

CN 100 | 50 | 50 | CN 100 | CN 100 | |

CN 55 | 90 | 9 | CN 55_2 | CN 55 | |

CN 55_1_R | 35 | 35 | CN 55_1 | CN 55_1 | |

CN 55_2_R | 90 | 90 | CN 55_2 | CN 55_2 | |

CN 55_3_R | 58 | 58 | CN 55_3 | CN 55_3 | |

CN 70 | 92 | 21 | CN 70_3 | CN 70 | |

CN 70_1_S | 50 | 50 | CN 70_1 | CN 70_1 | |

CN 70_2_S | 65 | 65 | CN 70_2 | CN 70_2 | |

CN 70_3_S | 92 | 92 | CN 70_3 | CN 70_3 |

Calculations for row with CN 101

max =IFERROR(IF(COUNTIF(B2,"*p*"),(AGGREGATE(14,6,Data!$C:$D/(LEFT(Data!$A:$A,LEN(A2))=A2&""),1)),(AGGREGATE(14,6,Data!$B:$B/(LEFT(Data!$A:$A,LEN(A2))=A2&""),1))),"no matches")

min =IF(COUNTIF(B2,"*p*"),(AGGREGATE(15,6,Data!$C:$D/(LEFT(Data!$A:$A,LEN(A2))=A2&""),1)),(AGGREGATE(15,6,Data!$B:$B/(LEFT(Data!$A:$A,LEN(A2))=A2&""),1)))

max location = (IF(COUNTIF(B2,"*p*"),(LEFT(INDEX(Data!A:A,MATCH(1,(A2&""=LEFT(Data!$A:$A,LEN(A2)))*IF((C2=Data!$C:$C),(C2=Data!$C:$C),(C2=Data!$D:$D)),0)),IFERROR(FIND("_S",INDEX(Data!A:A,MATCH(1,(A2&""=LEFT(Data!$A:$A,LEN(A2)))*IF((C2=Data!$C:$C),(C2=Data!$C:$C),(C2=Data!$D:$D)),0)))-1,IFERROR(FIND("_R",INDEX(Data!A:A,MATCH(1,(A2&""=LEFT(Data!$A:$A,LEN(A2)))*IF((C2=Data!$C:$C),(C2=Data!$C:$C),(C2=Data!$D:$D)),0)))-1,IFERROR(FIND("_T",INDEX(Data!A:A,MATCH(1,(A2&""=LEFT(Data!$A:$A,LEN(A2)))*IF((C2=Data!$C:$C),(C2=Data!$C:$C),(C2=Data!$D:$D)),0)))-1,(LEN(INDEX(Data!A:A,MATCH(1,(A2&""=LEFT(Data!$A:$A,LEN(A2)))*IF((C2=Data!$C:$C),(C2=Data!$C:$C),(C2=Data!$D:$D)),0))))))))),(LEFT(INDEX(Data!A:A,MATCH(1,(A2&""=LEFT(Data!$A:$A,LEN(A2)))*(C2=Data!$B:$B),0)),IFERROR(FIND("_S",INDEX(Data!A:A,MATCH(1,(A2&""=LEFT(Data!$A:$A,LEN(A2)))*(C2=Data!$B:$B),0)))-1,IFERROR(FIND("_R",INDEX(Data!A:A,MATCH(1,(A2&""=LEFT(Data!$A:$A,LEN(A2)))*(C2=Data!$B:$B),0)))-1,IFERROR(FIND("_T",INDEX(Data!A:A,MATCH(1,(A2&""=LEFT(Data!$A:$A,LEN(A2)))*(C2=Data!$B:$B),0)))-1,(LEN(INDEX(Data!A:A,MATCH(1,(A2&""=LEFT(Data!$A:$A,LEN(A2)))*(C2=Data!$B:$B),0)))))))))))

min location =(IF(COUNTIF(B2,"*p*"),(LEFT(INDEX(Data!A:A,MATCH(1,(A2&""=LEFT(Data!$A:$A,LEN(A2)))*IF((D2=Data!$C:$C),(D2=Data!$C:$C),(D2=Data!$D:$D)),0)),IFERROR(FIND("_S",INDEX(Data!A:A,MATCH(1,(A2&""=LEFT(Data!$A:$A,LEN(A2)))*IF((D2=Data!$C:$C),(D2=Data!$C:$C),(D2=Data!$D:$D)),0)))-1,IFERROR(FIND("_R",INDEX(Data!A:A,MATCH(1,(A2&""=LEFT(Data!$A:$A,LEN(A2)))*IF((D2=Data!$C:$C),(D2=Data!$C:$C),(D2=Data!$D:$D)),0)))-1,IFERROR(FIND("_T",INDEX(Data!A:A,MATCH(1,(A2&""=LEFT(Data!$A:$A,LEN(A2)))*IF((D2=Data!$C:$C),(D2=Data!$C:$C),(D2=Data!$D:$D)),0)))-1,(LEN(INDEX(Data!A:A,MATCH(1,(A2&""=LEFT(Data!$A:$A,LEN(A2)))*IF((D2=Data!$C:$C),(D2=Data!$C:$C),(D2=Data!$D:$D)),0))))))))),(LEFT(INDEX(Data!A:A,MATCH(1,(A2&""=LEFT(Data!$A:$A,LEN(A2)))*(D2=Data!$B:$B),0)),IFERROR(FIND("_S",INDEX(Data!A:A,MATCH(1,(A2&""=LEFT(Data!$A:$A,LEN(A2)))*(D2=Data!$B:$B),0)))-1,IFERROR(FIND("_R",INDEX(Data!A:A,MATCH(1,(A2&""=LEFT(Data!$A:$A,LEN(A2)))*(D2=Data!$B:$B),0)))-1,IFERROR(FIND("_T",INDEX(Data!A:A,MATCH(1,(A2&""=LEFT(Data!$A:$A,LEN(A2)))*(D2=Data!$B:$B),0)))-1,(LEN(INDEX(Data!A:A,MATCH(1,(A2&""=LEFT(Data!$A:$A,LEN(A2)))*(D2=Data!$B:$B),0)))))))))))

Data tab

CN | Value 1 | Value 2 | value 3 |

CN 101 | 3 | 2 | 1 |

CN 101_1_T | 5 | 1 | 4 |

CN 101_2_T | 1 | 0.6 | 0.4 |

CN 101_3_T | 10 | 7 | 3 |

CN 100 | 50 | 30 | 90 |

CN 55 | 9 | 75 | 6 |

CN 55_1_R | 35 | 50 | 88 |

CN 55_2_R | 90 | 1000 | 2 |

CN 55_3_R | 58 | 63 | 678 |

CN 70 | 21 | 31 | 9 |

CN 70_1_S | 50 | 99 | 45 |

CN 70_2_S | 65 | 44 | 78 |

CN 70_3_S | 92 | 95 | 235 |

I would appreciate any guidance here. If this is not clear and you need additional information I will do my best to provide it.

Thanks in advance!