Excel worksheet formula question

Copper Contributor

I am using PC Windows 11 and Microsoft 365. I am creating a yearly workbook with 12 worksheets (tabs). I want to create formulas that involve data from the prior worksheet. For example, on worksheet 2, I want a formula that takes a cell value from worksheet 2, subtracts a value from worksheet 1, and calculates the new value on worksheet 2. I have done this, but only by clicking on cells I want used in the calculation (in a cell on W2, I type "=", click on a W2 cell, type "-", click on a W1 cell, and press enter). This works, but now I want to copy W2, rename it W3, and not have to go into each cell and recreate the formula, because the formulas on W3 will refer to the W2 and W1 cells used on W2. I need the formula to include "prior page cell D7" instead of "W2 cell D7", so that each pages' formulas will refer to the prior page (whatever its name is), and not the "name" of the prior page.

 

9 Replies

@Mgoldwich 

If I may recommend adding a sample file (without sensitive data) with your plans, it can do miracles :)).

Not everyone who wants to help will do this thought-persecution.

 

Thank you for your understanding and patience

 

NikolinoDE

I know I don't know anything (Socrates)

@NikolinoDE I don't mind doing that, but I don't know how to attach the workbook file. 

@Mgoldwich 

 

Drag and drop here or browse files to attach
Maximum size: 71 MB • Maximum attachments allowed: 5
 

Hope this help :)

 

 

NikolinoDE

I know I don't know anything (Socrates)

@NikolinoDE error message "The file type (.xlsx) is not supported."

@Mgoldwich 

That formula should be enough for you project and it should also work :),

you can then transfer this to your cells as you wish.

=SUM('Jan 2022'!B3)-SUM('Dec 2021'!B3)

 

Hope I was able to help you with this info.

 

NikolinoDE

I know I don't know anything (Socrates)

it doesn't work when I copy/create a new worksheet, because on the new worksheet, it keeps the same formula, =SUM('Jan 2022'!B3)-SUM('Dec 2021'!B3) when the new formula needs to be =SUM('Feb 2022'!B3)-SUM('Jan 2022'!B3). I'm hoping there is a formula that indicates like "=SUM('prior worksheet'!B3)-SUM('current worksheet'!B3". That way, no matter how many times you copy a worksheet and add it to the end of the workbook, the formulas are in place and don't need to be changed. Does that make sense?

@Mgoldwich 

Relative, Absolute, and mixed references

=SUM('Jan 2022'!$B$3)-SUM('Dec 2021'!$B$3)

with a dollar sign ($)

 

 

Copy a worksheet in the same workbook

Press CTRL and drag the worksheet tab to the tab location you want.

OR

Right click on the worksheet tab and select Move or Copy.

Select the Create a copy checkbox.

Under Before sheet, select where you want to place the copy.

Select OK.

 

Hope I was able to help you with this info.

 

NikolinoDE

I know I don't know anything (Socrates)

it did not work. first, when I tried to drag the new formula down the column, the formula remained the same in each row. they all said "B$3". usually, as you drag a formula down, the next row will say "B$4", and so on. also, when I copied the worksheet, and then renamed the new copy as "Jun", the "Jan" in the formula DID change to "Jun", but the "Dec" stayed the same, when it should have changed to "May".

@Mgoldwich 

That's how it should be :)
As you described at the beginning, B$ should be taken as a fixed reference, as should the formula.
You just need to delete the $ and then drag it down, that's how the cells change too.

 

instead of

=SUM('Jan 2022 (2)'!$B$3)-SUM('Dec 2021'!$B$3)

use the formula without $

=SUM('Jan 2022 (2)'!B3)-SUM('Dec 2021'!B3)

nd then drag it down

 

Here is some basic information for relative and absolute cell references.

Switch between relative, absolute, and mixed references

 

Hope I could help you with these information / links.

 

NikolinoDE

I know I don't know anything (Socrates)