kev-griggs

asked on

# Offset Variables in Formulas Excel Macro VBA 2010

Hi,

I have an excel spreadsheet (see attached) where I need to loop through each row looking at the value in 'G' (starting at G4).

What I would like it to do is monitor the times taken in the 6 different sections, so based on the entry in there (i.e. 'G1') look to the values on Sheet 2 (in column A), match it, and put the date in column X minus the relevant amount of days in each column.

For Example, because cell G4 = 'G1', the value for L4 (Panels) will be X4 -1-1-7-5-6-10 (all the sections on sheet 2 corresponding to G1, and then M4 (In Rig) will be X4 -1-1-7-5-6 but not the 10 as the panels takes 10 days according to the test values in sheet 2.

Lastly, I only want this to be applied if the values in L,M,N,O,V and W are blank (as we will replace with percentages when they are in the relevant sections and X's when they are completed and moved on).

Would be grateful for any pointers, I have attempted an offset and a loop method but this hasn't been successful.

Regards,

Ben

Test.xlsm

I have an excel spreadsheet (see attached) where I need to loop through each row looking at the value in 'G' (starting at G4).

What I would like it to do is monitor the times taken in the 6 different sections, so based on the entry in there (i.e. 'G1') look to the values on Sheet 2 (in column A), match it, and put the date in column X minus the relevant amount of days in each column.

For Example, because cell G4 = 'G1', the value for L4 (Panels) will be X4 -1-1-7-5-6-10 (all the sections on sheet 2 corresponding to G1, and then M4 (In Rig) will be X4 -1-1-7-5-6 but not the 10 as the panels takes 10 days according to the test values in sheet 2.

Lastly, I only want this to be applied if the values in L,M,N,O,V and W are blank (as we will replace with percentages when they are in the relevant sections and X's when they are completed and moved on).

Would be grateful for any pointers, I have attempted an offset and a loop method but this hasn't been successful.

Regards,

Ben

Test.xlsm

ASKER

Hi Brad,

One quick thing, how do I expand the formula for the other sections (Paint, Finals etc)

I've given it a go but not having much luck here!

Ben

One quick thing, how do I expand the formula for the other sections (Paint, Finals etc)

I've given it a go but not having much luck here!

Ben

Ben,

I think I completely missed your aim. You are looking for a formula , not a macro--right? And what I saw as a concatenation, should really be a series of subtractions.

If so, then consider:

=$X4-SUM(INDEX(tbLookup,MATCH($G4,INDEX(tbLookup,,1),0),MATCH(L$2,INDEX(tbLookup,1,),0)):INDEX(tbLookup,MATCH($G4,INDEX(tbLookup,,1),0),COLUMNS(tbLookup)))

This formula is using a named range tbLookup that refers to worksheet StandardTimes cells A1:G12.

I used a Custom format of d\.m\.yy to get your dates to look like 13.3.13 for 13 March 2013.

When I copied the formula down, row 24 returned #N/A. Upon investigation, I found that cell G24 contained a space after "G2 ". After removing that space, the formulas worked.

Brad

Test-1Q28079674.xlsm

I think I completely missed your aim. You are looking for a formula , not a macro--right? And what I saw as a concatenation, should really be a series of subtractions.

If so, then consider:

=$X4-SUM(INDEX(tbLookup,MA

This formula is using a named range tbLookup that refers to worksheet StandardTimes cells A1:G12.

I used a Custom format of d\.m\.yy to get your dates to look like 13.3.13 for 13 March 2013.

When I copied the formula down, row 24 returned #N/A. Upon investigation, I found that cell G24 contained a space after "G2 ". After removing that space, the formulas worked.

Brad

Test-1Q28079674.xlsm

ASKER

Brad,

I really don't mind, I couldn't figure out a formula that worked, but this appears to, the only problem is this will be constantly added to, so new lines will be added and pasting the formula down might be tricky for the end user (maybe the macro was along the right track, just not for all sections?).

Ben

I really don't mind, I couldn't figure out a formula that worked, but this appears to, the only problem is this will be constantly added to, so new lines will be added and pasting the formula down might be tricky for the end user (maybe the macro was along the right track, just not for all sections?).

Ben

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Fantastic, Thank you very much Brad

ASKER

Really helpful and tweaked it a few times without any fuss.

ASKER

Hi Brad,

A really awkward moving target, they've now requested the dates going back are Working Days (Weekdays Mon-Fri) only. Would this be an easy change to implement?

Ben

A really awkward moving target, they've now requested the dates going back are Working Days (Weekdays Mon-Fri) only. Would this be an easy change to implement?

Ben

ASKER

did it! dont worry. thank you

"=WORKDAY(RC24,-SUM(INDEX(tbLookup,MATCH(RC7,INDEX(tbLookup,,1),0),MATCH(R2C,INDEX(tbLookup,1,),0)):" & _

"INDEX(tbLookup,MATCH(RC7,INDEX(tbLookup,,1),0),COLUMNS(tbLookup))))"

"=WORKDAY(RC24,-SUM(INDEX(

"INDEX(tbLookup,MATCH(RC7,

Open in new window

Brad