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

Microsoft ExcelVB ScriptSpreadsheets

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

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!

Walt Forbes

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

Log in or sign up to see answer

Become an EE member today7-DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform

or

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.

Not exactly the question you had in mind?

Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.

ask a question
Fantastic, Thank you very much Brad

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

Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

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

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