Linking a calculation in one worksheet to another

[Montreal_Mike]

Hi, I am using Excel for windows (office 365 edition), and am trying to link the results of a formula calculated on one worksheet to a cell in another worksheet. Specifically, I am calculating the expenses incurred for one month using a formula on worksheet A, and want the result of that calculation to show up on worksheet B, where I am tabulating my annual expenses for that category. I would then want to sum up all the monthly results in worksheet B, to get my overall spend for the year.

So, worksheet A calculates the January spend, worksheet A2 calculates the February spend, etc., and worksheet B would take the result from worksheet A, worksheet A2, and so on, and calculate the total amount.   That way, if I missed a January expense, I could add it to worksheet A, which would update the total January spend, and worksheet B would be updated as well.

When I set up the cell in worksheet A, I either get a #name error message in Worksheet B, or a hyperlink in worksheet B (i.e. an actual hyperlink, not the result itself, so I can't add up the monthly values).

What am I missing?

[#2]

@Montreal_Mike 

With your permission, if I can recommend you, add a file (without sensitive data) to your project.

Explain your plans in relation to this file. So you can get a solution that is tailored to your needs much faster.

At the same time, it is much easier for someone who wants to help to understand the subject.

A win-win situation for everyone.

Please no Picture, even if it is said that a picture can say a thousand words, it is certainly not always in the case of Excel, on the contrary in some cases.

* Knowing the Excel version and operating system would also be an advantage.

Finally, please consider why does one of the helpers still have to prepare the file with your request?

Thank you for your understanding and patience

Nikolino

I know I don't know anything (Socrates)

[#3]

@NikolinoDE 

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!

[#4]

@Montreal_Mike 

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

[#5]

@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.

[#6]

@NikolinoDE

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!

[#7 · Accepted solution]

@Montreal_Mike 

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.

[#8]

Thanks Sergei!

[#9]

@Montreal_Mike , you are welcome