CODEWITHSUNDEEP
excelexcel-formulaexcel-2010ms-officeexcel-2007
JesBet
JesBet

Reputation: 9

Is there a formula or method of automatically Multiplying Data on a particular sheet and across diffterent sheets

I am creating a report document which will automatically calculate the total marks earned by a student in a classroom. The values to be multiplied are in cell B22 and E22 of Sheet1, Sheet2, Sheet3 and to display the result in Sheet4. Sheet1, Sheet2 and Sheet 3 have the same formatting and design. What is the method or Formula that can be used to multiply;

B22*E22 in Sheet1 which will display the result in sheet4,

and when i use the auto fill handle on the cell containing the result in sheet4, excel should follow that pattern to multiply the same cells across the various sheets automatically;

let's say

B22*E22 in Sheet2

B22*E22 in Sheet3

and so on...

I have tried calculating it manually;

Let's say on Sheet4 in Cell A1, i used

='Sheet1'!B22*'Sheet1'!E22

in Cell A2, ='Sheet2'!B22*'Sheet2'!E22

in Cell A3, ='Sheet3'!B22*'Sheet3'!E22

and it worked. But the problem here is that, this work is purely manual and time consuming since i have a lot of sheets (about 62 sheets) and a lot of other cell calculations to make similar to that above (in other workbooks).

When i input the formula ='Sheet1'!B22*'Sheet1'!E22 in cell A1, i tried using the auto fill handle to drag and fill the cells A2 and A3.

But it didn't work as excel keeps changing the cell references.

This is the formula i used which works on Sheet4

in A1 ='Sheet1'!B22*'Sheet1'!E22

When i use the auto fill handle, to fill the formula and have the values in

A2, this is what it gives ='Sheet1'!B23*'Sheet1'!E23

A3, this gives ='Sheet1'!B24*'Sheet1'!E24

I want a formula or method through which when i use the field handle, excel should Maintain the Cells reference for calculation (Multiplication), that is,

B22 and E22

and rather change the sheets instead. That is,

On Sheet4, in cell A1: ='Sheet1'!B22*'Sheet1'!E22

A2: ='Sheet2'!B22*'Sheet2'!E22

A3: ='Sheet3'!B22*'Sheet3'!E22.

The formulae above is the result i expect to have.

But the actual output is

On Sheet4, in cell

A1: ='Sheet1'!B22*'Sheet1'!E22

A2: ='Sheet1'!B23*'Sheet1'!E23

A3: ='Sheet1'!B24*'Sheet1'!E24.

Any help/clue will be highly appreciated!

Thanks in advance.!

Upvotes: 0

Views: 296

Answers (2)

Solar Mike
Solar Mike

Reputation: 8375

So lots of options shown working, B22 left blank to prove calculation works. Used multiply instead of add as that was what you had, also changed "" to 0 to get the correct functionality.

enter image description here

Upvotes: 0

Forward Ed
Forward Ed

Reputation: 9874

=INDIRECT("'Sheet"&ROW(A1)&"'!B22")*INDIRECT("'Sheet"&ROW(A1)&"'!E22")

Place the above formula in an empty cell then copy down.

The INDIRECT function converts text with references to an excel reference. Something to be aware of though is that INDIRECT is a volatile function. This is not the end of the world. A volatile function simply means it recalculates anytime anything in the workbook changes. A regular function will only recalculate when something that affects it changes. In other words, if you have a workbook full of volatile functions you may notice some performance issues with the workbook due to all the calculation happening anytime a cell was changed

Update

Apparently I should listen to my own write up. INDIRECT converts references. Apparently it will not work with the math operator. So use indirect for each reference and keep your math operators out of the indirect.

POC

The above image shows a copy and paste of the above formula. IF the sheet does not exist it will toss an error as it does not have an address to find.

Upvotes: 1

Related Questions