Copy formula down a column that calculates cells across a row

[SewStage]

Hi, brand new to MrExcel; I tried searching for previous posts about this but I'm not sure I'm asking the question correctly; so I apologize if this is listed elsewhere.

I have a formula in G5 of that I want to copy down to G50: =SUM(INDEX('Material by Mo.'!$D2:$O2,MONTH(TODAY()))*'Material Usage'!H$2):

Copy/fill works as it should for $D2, $D3, etc., however, the multiplier in the formula ('Material Usage'!H$2) that corresponds to each of the items/rows in column D above is actually across columns on a different tab:

Is there a way to perform a copy/fill of the formula in G6-50 that will also adjust the column letters as well - D2*H2, D3*I2, D4*J2, D5*K2? Right now when I copy/fill down column G they all say H$2. Hopefully that all made sense and I explained it effectively. Thanks much!!

 

[Marcílio_Lobão]

SewStage, Good evening.

Try to substitute:

From...: 'Material Usage'!H$2

To.........: OFFSET('Material Usage'!G$2;0;ROW(A1))

Please, tell us if it worked for you.

I hope it helps.

 

[SewStage]

SewStage, Good evening.

Try to substitute:

From...: 'Material Usage'!H$2

To.........: OFFSET('Material Usage'!G$2;0;ROW(A1))

Please, tell us if it worked for you.

I hope it helps.

Hi Marcilio, thanks so much for your response. The formula itself does work, but it's not returning the correct value and I can't determine which fields its actually grabbing/calculating; the answer to the formula should be $69.16 and it's returning a value of $348.08. Thoughts? Again - thank you!

 

[Marcílio_Lobão]

SewStage, Good morning.

Seeing only pieces of images is much more difficult to help.

What you asked for is solved.

Is it possible for you to show us a larger part of the spreadsheet where there is "'Material by Mo.'! $D2: $O2"?

In your formula the part:

a) INDEX ('Material by Mo.'!$D2:$O2, MONTH(TODAY())) returns only one number.

b) 'Material Usage'!H$2 also returns only one number.

So why do you need the SUM function?

I must not have correctly understood your need completely.

Maybe someone has a much better perception of your need than I do.

 

[Fluff]

How about

=INDEX('Material by Mo.'!$D2:$O2,MONTH(TODAY()))*INDEX('Material Usage'!H$2:BE$2,ROWS(G$5:G5))

 

[SewStage]

How about

=INDEX('Material by Mo.'!$D2:$O2,MONTH(TODAY()))*INDEX('Material Usage'!H$2:BE$2,ROWS(G$5:G5))

YES!!! This works! Thank you so much @Fluff !

 

[SewStage]

SewStage, Good morning.

Seeing only pieces of images is much more difficult to help.

What you asked for is solved.

Is it possible for you to show us a larger part of the spreadsheet where there is "'Material by Mo.'! $D2: $O2"?

In your formula the part:

a) INDEX ('Material by Mo.'!$D2:$O2, MONTH(TODAY())) returns only one number.

b) 'Material Usage'!H$2 also returns only one number.

So why do you need the SUM function?

I must not have correctly understood your need completely.

Maybe someone has a much better perception of your need than I do.

I'm so sorry, Marcilio, if my information wasn't inclusive enough. Another response did do the trick though. Thank you again for your time on this; very much appreciated!

 

[Fluff]

Glad we could help & thanks for the feedback.