# Copy formula down a column that calculates cells across a row

#### SewStage

##### New Member
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!!

#### Attachments

• 1616284993081.png
3.5 KB · Views: 4

### Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

#### Marcílio_Lobão

##### Well-known Member
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

##### New Member
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!

#### Attachments

• 1616299554306.png
4.6 KB · Views: 3

#### Marcílio_Lobão

##### Well-known Member
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"?

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

##### MrExcel MVP, Moderator

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

#### SewStage

##### New Member
Excel Formula:
``=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

##### New Member
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"?

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

##### MrExcel MVP, Moderator
Glad we could help & thanks for the feedback.

Replies
13
Views
174
Replies
6
Views
63
Replies
11
Views
261
Replies
12
Views
103
Replies
10
Views
290

1,130,172
Messages
5,640,584
Members
417,152
Latest member
DayTimeSeby

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back