Tuesday, January 16, 2018

yogi_Calculation Cell Referencing Across Multiple Tabs In A Spreadsheet

Google Spreadsheet   Post  #2345

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Jan-16-2018
Calculation referencing multiple cells and across tabs
I have a spreadsheet with multiple tabs for inputs: Money we have received (Budget), Money we have spent (Actual Spent), and Balance (where I want to calculate).  All data is tied to a client name in column A.

What I want to do is be able to have a roll-up sheet (Balance) with a formula that will reference the input in Column A (client name) and in Row 1 (month/year) and calculate the difference between those values in the other tabs.  This would be used in cell H2 and copied down the line.

For example the sample client I have entered has a budget for each month in the Budget tab.
Actual spends are entered in the next tab.
in Balance, I want to calculate the difference between Budget and Actual as it relates to the client in column A, and if possible, reference Row 1 so that it will just look up those values so I don't have to mess with it down the road as long as the naming structure is consistent into 2018, 2019, and so on as we add dates down the line.

Here is the sample data I've entered into a sheet to share: 

Any help is appreciated!  I've been mapping out how to make this work and keep running into a wall.


Thanks,
Amanda