Help with a macros to use in Excel 2010 workbook

Kobyexcel 1

New member
Joined
May 8, 2014
Messages
2
Reaction score
0
Points
0
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 D2:D11 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
 

Attachments

  • Earliest date finder help.xlsx
    11.8 KB · Views: 6

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
Just use the formula

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

in C2, and copy down
 

Kobyexcel 1

New member
Joined
May 8, 2014
Messages
2
Reaction score
0
Points
0
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
 
Top