Results 1 to 3 of 3

Thread: Help with a macros to use in Excel 2010 workbook

  1. #1

    Help with a macros to use in Excel 2010 workbook



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I am new to VBA/macros and need help with the following problem I am having with my Excel sheet and was told a “macros” will be the best way to go.

    Problem:

    Please see sheet attached; I wrote the following MS Excel (2010) syntax in cells D211 to help me determine the "Earliest start date” (column D) in the range of each project ID number (column A):
    =IF(C2="","",(SMALL($C$2:$C$11,1)))

    How do I copy and paste the formula into the next groups of 10 cells for each project ensuring that the reference range is accurate? Any help with a "macros", because if I copy and paste the range of 10 rows for each project ID as absolute ("$C$2:$C$11") I see that it carries it over to the next set of 10 rows, which should be $C$12:$C$21) instead, and so on. As you can imagine, without a macros, I will have to go and manually change the range, which is tedious for a large Excel sheet of about 500 projects (times 10 project teams = 5000 cells to manually enter).

    Thank you in advance for your help.

    Koby
    Attached Files Attached Files

  2. #2
    Just use the formula

    =MIN(OFFSET($C$2,(INT((ROW(A2)+8)/10)-1)*10,0,10,1))

    in C2, and copy down

  3. #3
    Wow! Thanks a Million times over Bob for the help. I prefixed it with an IF statement to take care of empty date cells. So the resulting formula I pasted into C@ is

    =IF(C2="","",(MIN(OFFSET($C$2,(INT((ROW(A2)+8)/10)-1)*10,0,10,1))))

    Very much appreciated the quick response and help.

    Koby

Posting Permissions

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