Trouble with named cell within a table when exchanged with cell references

[derek davis]

Guys, could I ask please if you could take a look and advise.
I have been looking into making this sheet easier to read from a function point of view i.e. l would like to use dynamic defind names cell references instead of using actual cell references. When I exchanged the cell references to defind names I keep getting error msg's coming up and at a loss for what is causing this issue and more importantly how to correct it. I have listed the functions that are bothering me. I am not an expert at all with and defind names functions but have tried various functions but unable to come up with any success. I have attached an example sheet to show where the issues occur. The three issues listed below along with their cell reference, any help would be most appreciated.

cell W5 holds this formula IF(W4="","",SUMPRODUCT($I$5:$I50,--(TEXT($J$5:$J50,"MMM")=$V5),--(TEXT($J$5:$J50,"yyyy")=TEXT(W$2,0))))
to read
IF(W4="","",SUMPRODUCT(Date,--(TEXT(Joint_PL,"MMM")=$V5),--(TEXT(Joint_PL,"yyyy")=TEXT(W$2,0))))


cell P2 Holds this Formula IF(Q2<TODAY(),SUMIFS(Monthly_Fees_1,Date,">="&Year_Start,Date,"<="&Year_End)+SUMIFS(Monthly_Fees_2,Date,">="&Year_Start,Date,"<="&Year_End),"")


This cell works correctly at present, but I would like to be able to sum the current month for the whole month, the data source is (Joint_PL Column I)
cell N7 holds this formula IF(Table15[[#This Row],[New Month]]<>"", LOOKUP(Table15[[#This Row],[New Month]]+1,[New Month],[Amounts]),"")

Thanks for any support in advance.

 

[eibi]

Derek --

Breaking your original post into its constituent parts:

=================

Looking first at cell W5.

In the sample file you provided, when I pull up the Name Manager to check on the definition of Joint_PL, I see a #REF! error. [It appears to refer to a different Excel file on your OneDrive.]

I think that it's as simple as re-defining this Name with a real reference. To test, I've re-defined that Name with the cell reference from the existing formula in cell W5. Joint_PL=$J$5:$J$50

=================

Looking next at cell P2.

Your original post doesn't give many specifics as to your intent with this formula, so I'm taking some liberties to guess at what you need...

First, it looks as though you've defined the names Monthly_Fees_1 and Monthly_Fees_2 and Date to refer to the header of the column only -- but I think you'll need to change that to refer to the data in the column instead.

For example:
=Table15[[#Headers],[Monthly Fees 1]] -- should instead be =Table15[Monthly Fees 1]

Second, similarly, you've defined the names Year Start and Year End to refer to the header rather than the cell containing the data in the respective column.

For example:
=Table15[[#Headers],[Year Start]] -- should instead be =Table15[@Year Start]

=================

Looking last at cell N7.

I'm afraid that I can't understand your intent from the LOOKUP() formula. It almost appears that in the month of May (per cell K7), you are wanting to add [+1] month (June) and sum the Joint_PL entries for that month?

Would need more specifics in order to be helpful on this one.

=================

Hope this gets you started in the right direction...!

 

[derek davis]

Thank you eibi for taking a look at this problem I am trying to resolve. I have made alterations to my master sheet and completed testing with success.

Here is some clarity on my problem on my 3rd question (N7) maybe I shouldn't be using Lookup. I'm not a expert by any means just a kean amature. What my intension was, to sum the current month and stops using maybe EOMonth function.
I want to see the results of cells N7, k7& O7 to appear from the end of the month. As the xls is designed as a financial investment record. However we have to mindful to include the change of the year. I have tried to include a ROI ( return on investment) as a % in (O7). At present the calculation doesn't appear to take into account of the whole month.