Forum Discussion
Linking a calculation in one worksheet to another
As a comment, it's necessary to type such formula. Start typing = into the cell, after that click on L3 in sheet March, Enter.
However, much more reliable will be to use formulas to return data into the summary sheet. Initial variant could be
with that to keep exactly the same Expense list for each month. You may add only actual expenses in the month, order is also not important. Formula is
=IFNA(INDEX(March!$L:$L,MATCH($B3,March!$B:$B,0)),"")That could be other improvements, but at least this one.
Thanks, an example is probably easier to follow (see attached file). On the first worksheet ("annual"), I list various types of expenses, their annual budget, and how much I could spend per month.
Each other worksheet (I only included those for January, February and March), I enter the actual amounts I spend during the months on those categories, and I use a simple "SUM" formula to get the total.
What I want to do is have that total monthly amount (the result of the formula) be linked to the annual worksheet, so that the monthly amounts ($289, $262, $413), show up in the annual worksheet, and the formulae in the annual worksheet would then tell me if my spending was on target.
For this example, only the "housekeeping" row has formulas and data.
When I "link" the annual worksheet to the cell in the "january" worksheet with the formula, I get a hyperlink in the "annual" worksheet. When I simply copy the cell from the "february" worksheet with the formula, I copy the formula itself, not the result of that formula applied to the "february" worksheet.
Thanks in advance for any suggestions!
I'm not sure if I understood the translation, but I think look for such a solution ... Please see the inserted file.
Thank you for your understanding and patience
Nikolino
I know I don't know anything (Socrates)
* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here
- NikolinoDE
Thank you, but that is not quite it.
In my example, the total amount for January is $289, so I would want that number to show up in the "annual" F3 cell, the total amount for february is $262, so I would want that number to show up in the "annual" G3 cell, and the March amount of $413 in the "annual" H3 cell.
Using "link" puts in a hyperlink to the right cells, but the "annual" worksheet can't calculate the annual spend, unless the numbers are in the "annual" cells.I found the answer: I need to use a formula of " =[name of worksheet]![cell reference]
So to get the result I want, in cell H3 in the "annual" workbook, i inserted the formula "=March!L3 "
L3 is the cell with the calculation, and if ever change the numbers in the march workbook, the calculation in L3 changes, and the number in H3 in the "annual" workbook updates as well.
Thanks for your time Nikolino!
As a comment, it's necessary to type such formula. Start typing = into the cell, after that click on L3 in sheet March, Enter.
However, much more reliable will be to use formulas to return data into the summary sheet. Initial variant could be
with that to keep exactly the same Expense list for each month. You may add only actual expenses in the month, order is also not important. Formula is
=IFNA(INDEX(March!$L:$L,MATCH($B3,March!$B:$B,0)),"")That could be other improvements, but at least this one.