Getting the Min for Moving Range with Locked Cells

[RoxannaBannana]

Hello Experts,

I am working on a sheet that should return the Min (Date) of specifc range (19 cells in total). This range is controlled by serial number (each change in column A). Working on large volume of data and I need to copy/paste this formula to reflect that. I tried copying this formula and the outcome was that I had to change the range everytime. I want to have that automated. Any support you can provide would be appreciated.

Is there a way to do that in Excel? Any suggestions would help!

Thank you so much!

Roxanne

[RoxannaBannana]

Sorry, forgot to mention that the formula that I am currently working with is located in column F.

[FDibbins]

Hi Roxanna and welcome to the forum

See if this is what you want? I condensed your formula using and(), and removed the absolutes ($)
=IF(AND(B5<$E$1,E5<$E$2,B5<$G$1,E5<$E$2,B5<$I$1,E5<$I$2),MIN(C5:C23),B5)

[RoxannaBannana]

@ PMFDibbins

Thank you so much for the prompt response.

The shortening of the formula worked, however, I am still struggling with the range. In specific, the C5 - C23 for the first block of data should remain the same. For example, I want all rows from 5 - 23 to reference the cells C5:C23. In addition, once I copy this formula down to the second block of U_RCN (column A) I want it to calculate the next block.

So, we need to add another argument to our formula to lookup column A and change that block at each change in U_RCN. I hope this makes sense.

Locking the cells for the C5-C23 does not help when I have to copy/past for the next block of data.

Your help is greatly appreciated.

Roxanna

[dredwolf]

what constitutes a "change" in U_RCN, they are all different as far as I can see, so how do you determine what a "change" is there ?
if its because the block color changes, well, I can't think of a way to detect that using formulas..
If it's just every 19 rows, then maybe:
Try this:

Please Login or Register  to view this content.

Drag down to bottom of list

I used or instead of and, and got exact same results as your sample showed

Hope this helps

Edit-
The Or is what your original nested if was saying, IF any of these are true, then do this, else that, AND say's IF all of these are true, then this, else that