Jan 11 2022 09:49 AM
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.
Jan 11 2022 10:08 AM
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
I know I don't know anything (Socrates)
Jan 11 2022 11:29 AM
@NikolinoDE I don't mind doing that, but I don't know how to attach the workbook file.
Jan 11 2022 11:53 AM
Jan 11 2022 12:16 PM
@NikolinoDE error message "The file type (.xlsx) is not supported."
Jan 12 2022 06:05 AM
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.
I know I don't know anything (Socrates)
Jan 12 2022 06:59 AM
Jan 12 2022 10:30 AM
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.
I know I don't know anything (Socrates)
Jan 18 2022 12:50 PM
Jan 19 2022 02:16 AM
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)