Formula help for creating portfolio valuation

Hello All,

I am trying to find a formula that will solve a problem of calculating net worth of invested amount across different sheets of the same workbook.

I am attaching the rough work book here. In this under invested tab column C should be the "addition of all units allotted till date (column E) * NAV of that month (Column D)" from all the sheets.

I have highlighted the required fields in each tab and have also mentioned manual formula in the cells above C8 under invested tab.

Thanks in advance.

Rushi.

With respect you are making the same mistake that I see time and time again. You are mixing up the two elements of data capture and final reporting. The two require quite different treatments.

A lot of people start by designing the form that they expect to see as the final report or which at first glance seems the best way of capturing data, and then wonder why it's so difficult to subsequently analyse and summarise or extract information from it. Yours exhibits all those features.


You should always capture data in a simple two dimensional table and worry about reporting information from it afterwards. Without exception doing this you will always be able to easily obtain management information. Rarely is this the case if you start the other way round.
You will also throw open the whole wonderful world of the powerful Pivot table functionality.

So before you get too far with this I'd create a single sheet database that contains the four columns you show on the data sheets along with a fifth column to hold the current sheets tab names

The way I normally arrange these things is to have a single data entry row above the database in which the new values are entered, then a button which runs a macro that adds the new record to the database.

Once you have a proper database then yuo'll be able to to use standard SUMIFS() to obtain your results as well as the aforementioned pivot table functinality

Please try at E3

=SUM(
IFERROR(LOOKUP(EOMONTH(B2,0),ABSL_T!B:D)*SUMIFS(ABSL_T!E:E,ABSL_T!B:B,"<"&EDATE(B2,1)),),
IFERROR(LOOKUP(EOMONTH(B2,0),BOIAXA_T!B:D)*SUMIFS(BOIAXA_T!E:E,BOIAXA_T!B:B,"<"&EDATE(B2,1)),),
IFERROR(LOOKUP(EOMONTH(B2,0),CanRob_E!B:D)*SUMIFS(CanRob_E!E:E,CanRob_E!B:B,"<"&EDATE(B2,1)),),
IFERROR(LOOKUP(EOMONTH(B2,0),Kotak_Std_M!B:D)*SUMIFS(Kotak_Std_M!E:E,Kotak_Std_M!B:B,"<"&EDATE(B2,1)),),
IFERROR(LOOKUP(EOMONTH(B2,0),'L&T_Emerging_B'!B:D)*SUMIFS('L&T_Emerging_B'!E:E,'L&T_Emerging_B'!B:B,"<"&EDATE(B2,1)),)
)