Formula places data in wrong table position

[Hojocolaca]

Thanks for taking the time to help solve this. It has gone beyond my capabilities or perhaps I cannot see the wood for the trees?

I have a spreadsheet designed to track my CPD requirements. Everything works well until I enter data for the months Jan - Mar of a given year.
The data is supposed to transfer to a calculation table based on the year entered, however the data is appearing in the wrong year of the table.

I cannot change the table, as it is formatted to the requirements of our governing Registration Board.
I need to have the data entered as Jan 13, 2013 showing in the calculation table for the year 2013, however it shows up as 2014.
This appears to be due to the months appearing as April - March on the tables.

This formula does not work on the Credit Record:

=SUMPRODUCT(--(MONTH(CME!$A$8:$A$40)=MONTH(Z$6)),--(YEAR(CME!$A$8:$A$40)=YEAR($A7)),(CME!$K$8:$K$40))

However this formula did work on a previous version:

=SUMIFS(CQI!K8:K31,CQI!A8:A31, "=Mar-13")

Not explaining myself well here - see the link for a better understanding (maybe).

https://www.dropbox.com/s/w6jfnr94jy...0-%202016.xlsx

TIA.

[dilipandey]

Hi hojocolaca,


welcome to the forum.
workbook appears to be protected at sheet level.. also give the references where to look into workbook i.e., which sheet /range / cell ?



Regards,
DILIPandey
<click on below * if this helps>

[Hojocolaca]

Hi

Thanks for your interest. I have unprotected the sheet (newbie error).
(FYI - I have left a password on the Credit Record page at bottom in case I have forgotten anything).

The numbers in RED on the Credit record page Z8 and AA8 are in the wrong year.
They should be in Z7 and AA7.

Sorry that this is so convoluted but I am having trouble explaining exactly what is wrong.

Cheers
Hojo

[dilipandey]

Hi Hojo,

Formula is fetching correct results and that too in correct cell, see the yellow cells in both the sheets to understand more.
Log Book 2012 - 2016.xlsx

Regards,
DILIPandey
<click on below * if this helps>

[Hojocolaca]

Hi Dili, I appreciate you taking the time to help.

The month cells at top (Q6-AB6), and the Year cells at side (P7-P10), combine to form a month / year table.
The year starts at April 2012, thus reading across the table Dec 2012 leads onto Jan 2013 (Y7-Y8).
Thus Z8 is actually Jan 2014, so that a date of Jan 2013 'should' show up in Z7.

I know these tables do not make much sense but this is the exact format that I have to submit the data in.
It is time consuming trying to collate all this by hand, so I figured this spreadsheet would be the best answer. Thought I had it right too, until Jan this year...

How do we tweak the formula to place the result in Z7? Have I just got the position wrong?

Cheers
Hojo

[dilipandey]

Hi Hojo,

reading across the table Dec 2012 leads onto Jan 2013 (Y7-Y8). Thus Z8 is actually Jan 2014, so that a date of Jan 2013 'should' show up in Z7.

No.. row 7 has a year in p7 which says 2012, not 2013 so how come you are expecting Jan2013 data in row 7 ?

Year 2013 is there in row 8 i.e., in p8 and hence I did not found any issue in the attached sheet. thanks.


Regards,
DILIPandey
<click on below * if this helps>

[Hojocolaca]

Aaaahh! I see it now. Thank you!
You are, of course, absolutely correct - but I could not see this - I have been looking at this for so long that it was becoming meaningless.

I have now corrected the formula to read from A8 instead of A7!
By adding a hidden cell on A11 and A21, with the correct forthcoming yearly value, I can extend the correct year to the last 3 remaining months (K10, L10, M10 - Jan, Feb, Mar 2016).

See it here - https://www.dropbox.com/s/w6jfnr94jy...0-%202016.xlsx

Thank you for your help.

Kind regards

Hojo

[dilipandey]

Great

Now, please mark this thread as [SOLVED].. thx


Regards,
DILIPandey
<click on below * if this helps>