We help IT Professionals succeed at work.

Excel sheet formula auto-fill

vkani
vkani asked
on
87 Views
Last Modified: 2017-04-08
Hi,
I have 3 sheets in an Excel file. On the second sheet, there's a 'total' row (9th row). On the first sheet, there are nearly 3500 rows of data.
On the third sheet, I want to produce each row in the 1st sheet, multiplied by the 9th row on the second sheet. I use formula to do this multiplication. For the first cell on Sheet3, i.e B2, the formula is something like this

='Sheet1'!B2*'Sheet2'!B9
Formula for C2='Sheet1'!C2*'Sheet2'!C9
.
.
.
Formula for Z2='Sheet1'!Z2*'Sheet2'!Z9

After filling the formula for B2, I just drag it sideways to auto-fill the other cells upto Z2. It works fine.
The problem starts when I drag the formula downwards. For example, when I drag it from B2 to B3, I get something like this.
 
='Sheet1'!B3*'Sheet2'!B10
But I want the same row on sheet2 to be multiplied with each row in Sheet1. i.e, I want
='Sheet1'!B3*'Sheet2'!B9 instead of ='Sheet1'!B3*'Sheet2'!B10.

I want the cell number to be incremented in sheet 1, but not in sheet 2.
Is there any way to do this?
Did I make my point clear?
Comment
Watch Question

ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
It would be helpful, if you provide sample workbook with expected result.
CERTIFIED EXPERT
Top Expert 2016
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Saqib HusainEngineer
CERTIFIED EXPERT

Commented:
Try

='Sheet1'!B3*'Sheet2'!B$9
Tahir QureshiSystem Analyst

Commented:
You have to use ='Sheet1'!B2*'Sheet2'!B$9.  "$" means that cell value is fixed so it will not change

Author

Commented:
Thank you Rgonzo1971. It works. I accepted your answer.
Thank you Saqib Husain, Syed - your answer came just 1 min after I got the solution. :)
CERTIFIED EXPERT
Top Expert 2016

Commented:
Not yet accepted though
CERTIFIED EXPERT
Top Expert 2016

Commented:
Accepted Solution according to Author's comment

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions