Copy formula down a column that calculates cells across a row

SewStage

New Member
Joined
Mar 16, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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):

1616284777103.png


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:

1616285004074.png


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
    1616284993081.png
    3.5 KB · Views: 4

Excel Facts

Add Bullets to Range
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
Joined
Oct 7, 2013
Messages
777
Office Version
  1. 2007
Platform
  1. Windows
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
Joined
Mar 16, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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
    1616299554306.png
    4.6 KB · Views: 3

Marcílio_Lobão

Well-known Member
Joined
Oct 7, 2013
Messages
777
Office Version
  1. 2007
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,883
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

SewStage

New Member
Joined
Mar 16, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,883
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top