help with offset

[paully]

I sure could use some help the formula below is on a summary worksheet and is the formula I for the current month. however, next month i need the formula to move two columns to the right.

=Mountain!AJ63*1000
next month =Mountain!AL63*1000
=Mountain!AJ71*1000
next month =Mountain!AL71*1000
=Mountain!AJ73*1000
=Mountain!AJ74*1000
=(Mountain!AJ80+Mountain!AJ81)*1000
=(Mountain!AJ87*1000)-YTDLaborBurden-CorpCharge-NonJobLabor

This is what I have so far for code:

Range("C8").Select
Selection.Formula = "=Offset(Mountain!AJ63,0,2)*1000"
Range("C10").Select
Selection.Formula = "=Offset(Mountain!AJ71,0,2)*1000"
Range("C11").Select
Selection.Formula = "=Offset(Mountain!AJ73,0,2)*1000"
Range("C12").Select
Selection.Formula = "=Offset(Mountain!AJ74,0,2)*1000"
Range("C15").Select
Selection.Formula = "=Offset(Mountain!AJ86,0,2)*1000"
Range("C16").Select
Selection.Formula = "=Offset(Mountain!AJ89,0,2)*1000"
Range("C18").Select
Selection.Formula = "=Offset(Mountain!AJ80,0,2),Offset(Mountain!AJ81,0,2)*1000"
Range("C19").Select
Selection.Formula = "=Offset((Mountain!AJ87,0,2)-YTDLaborBurden-PTDCorpCharge-PTDNonJobExpense))*1000"

[Jim Rech]

Instead of hardwiring the month offset like this:

=Offset(Mountain!AJ63,0,2)*1000

do something like this:

=Offset(Mountain!AJ63,0,A1*2)*1000

Then in cell A1 (in this example), enter a 1 for January, 2 for February,
etc. Then the offset of all the formulas will then adjust just by changing
one cell.


--
Jim
"paully" <[email protected]> wrote in
message news:[email protected]...
|
| I sure could use some help the formula below is on a summary worksheet
| and is the formula I for the current month. however, next month i need
| the formula to move two columns to the right.
|
| =Mountain!AJ63*1000
| next month =Mountain!AL63*1000
| =Mountain!AJ71*1000
| next month =Mountain!AL71*1000
| =Mountain!AJ73*1000
| =Mountain!AJ74*1000
| =(Mountain!AJ80+Mountain!AJ81)*1000
| =(Mountain!AJ87*1000)-YTDLaborBurden-CorpCharge-NonJobLabor
|
| This is what I have so far for code:
|
| Range("C8").Select
| Selection.Formula = "=Offset(Mountain!AJ63,0,2)*1000"
| Range("C10").Select
| Selection.Formula = "=Offset(Mountain!AJ71,0,2)*1000"
| Range("C11").Select
| Selection.Formula = "=Offset(Mountain!AJ73,0,2)*1000"
| Range("C12").Select
| Selection.Formula = "=Offset(Mountain!AJ74,0,2)*1000"
| Range("C15").Select
| Selection.Formula = "=Offset(Mountain!AJ86,0,2)*1000"
| Range("C16").Select
| Selection.Formula = "=Offset(Mountain!AJ89,0,2)*1000"
| Range("C18").Select
| Selection.Formula =
| "=Offset(Mountain!AJ80,0,2),Offset(Mountain!AJ81,0,2)*1000"
| Range("C19").Select
| Selection.Formula =
|
"=Offset((Mountain!AJ87,0,2)-YTDLaborBurden-PTDCorpCharge-PTDNonJobExpense))*1000"
|
|
| --
| paully
| ------------------------------------------------------------------------
| paully's Profile:
http://www.excelforum.com/member.php...o&userid=37417
| View this thread: http://www.excelforum.com/showthread...hreadid=572029
|

[paully]

Jim - Thank you for your help! It works great execpt for the two lines below. I need to sum the two offset cells * 1000. 2nd line i'm trying to offset, subtract the 3 cells, & then * 1000.



Range("C18").Select
Selection.Formula = "=Sum((Offset(Mountain!AJ80,0,c3*1) & offset(Mountain!AJ81,0,c3*1))*1000"

Range("C19").Select
Selection.Formula = "=Offset((Mountain!AJ87,0,c3*1)-c14-c15-c18))*1000"


Thank you for any help!

Paul