+ Reply to Thread
Results 1 to 5 of 5

Getting the Min for Moving Range with Locked Cells

  1. #1
    Registered User
    Join Date
    01-24-2013
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2007
    Posts
    3

    Getting the Min for Moving Range with Locked Cells

    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
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    01-24-2013
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Getting the Min for Moving Range with Locked Cells

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

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,926

    Re: Getting the Min for Moving Range with Locked Cells

    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)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    01-24-2013
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Getting the Min for Moving Range with Locked Cells

    @ 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

  5. #5
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Getting the Min for Moving Range with Locked Cells

    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:
    Formula: copy to clipboard
    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
    Last edited by dredwolf; 01-25-2013 at 04:05 PM.
    A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
    -Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
    -To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
    If you received helpful response, please remember to hit the * of that post

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1