Transpose? Offset? Variable starting cell

[Fin Analyst]

Good afternoon~

I have scoured the discussion boards and my mind, but can't seem to figure
this one out. Any help would be much appreciated. OK, here is my problem.
I have a chart listing %s (C20:C34). These %s represent how much cost should
be allocted to month 1 through (at most, but could be less) month 15 of a
production period. The problem is the starting month for the cost is
variable, in cell D75. When the month number in D75 matches the month number
in the array H4W4 the costs should start based on the %s input in C20:C34,
ie. if the 1st month of production % (C20) is 10% and the costs don't start
until month 10 (in this array would be Q4) then I need in Q75 to show the
result of 10%*G75 (total cost). In R75 the resulting formula would be
C21*G75 and so on until you reach the end of the production month period.
I'm thinking some sort of combination of OFFSET and TRANSPOSE would be in
order, but I can't exactly figure out how to word the formula. Maybe it
would be helpful to first transpose the chart %s first and go from there?
Please help if you have any ideas. Hopefully I am explaining my dilemna
appropriately so others can understand my question. If you have any
questions about my problem please post so that I may answer. Let me help you
help me.

Thanks in advance for your help.
Jaclyn

 

[Ardus Petus]

I'm not quite sure I understood your problem

Enter in H75 following formula, then copy right to DW75.
=IF(OR(COLUMN()-COLUMN($G75)<$D75,COLUMN()-COLUMN($G75)>=$D75+ROWS($C$20:$C$
34)),"",$G75*OFFSET($C$20,COLUMN()-COLUMN($G75)-$D75,0))

HTH

 

[Fin Analyst]

For not quite understanding the problem that formula looks like it did the
trick. Thank you so much! =)
(I keep forgetting about those row and column formulas.)

Jaclyn

 

[Fin Analyst]

I should also mention that I added an if/ then statement in case the
resulting production cost for the month is zero, ie if the production period
was less than the 15 months possible. Below is my final formula:

=IF(OR(COLUMN()-COLUMN($G60)<$D60,COLUMN()-COLUMN($G60)>=$D60+ROWS($C$20:$C$34)),"",IF($G60*OFFSET($C$20,COLUMN()-COLUMN($G60)-$D60,0)=0,"",$G60*OFFSET($C$20,COLUMN()-COLUMN($G60)-$D60,0)))

Thanks again for your help! Because of this formula I was able to delete
over 1,000 hidden rows from our previous template. The previous person set
up the calculation the long way.

Jaclyn

 

[Ardus Petus]

I'm glad I could help!

--
AP

I should also mention that I added an if/ then statement in case the
resulting production cost for the month is zero, ie if the production period
was less than the 15 months possible. Below is my final formula:

=IF(OR(COLUMN()-COLUMN($G60)<$D60,COLUMN()-COLUMN($G60)>=$D60+ROWS($C$20:$C$
34)),"",IF($G60*OFFSET($C$20,COLUMN()-COLUMN($G60)-$D60,0)=0,"",$G60*OFFSET(
$C$20,COLUMN()-COLUMN($G60)-$D60,0)))

Thanks again for your help! Because of this formula I was able to delete
over 1,000 hidden rows from our previous template. The previous person set
up the calculation the long way.

Jaclyn