My reference cell is not moving in a sumifs array- it isn't anchored

[TanyaPL]

(hyperlink to googlesheets version of my Microsoft Excel sheet- as I couldnt figure out how to attach the file!

https://docs.google.com/spreadsheets/d/10S4lg6PyUQC7wZjVp4Oepdnql5v4ii4GR091nm5Jzuw/edit?usp=sharing

Hi all,

I hope somebody can help! I will start with what I am looking for, and then what I have so far!

on the link above- I have 2 sheets. One titled "MRP" and one titled "graph". What I would like is for the values in row 15 of the graph sheet, to be displayed in row 5 of the MRP sheet (highlighted in yellow where the formula would be).

My issue is, that the graph sheet is set by month (cell has a value of 01/05/2018 and so on), and my MRP sheet breaks the month down into weeks. What I require, is - if on the MRP sheet, the dates in row 3 fall within a month(from the graph sheet), then display the figure. I also want to be able to drag this formula from row L in the MRP sheet, to row AF.

The formula I have tried so far beings back the figure in June- but displays the figure for June in all weeks, which isn't right. This is because in the formula, the reference of "N3" doesn't drag along, even without an anchor. the formula is in the MRP sheet linked above, but also below, for reference. (this was in Microsoft Excel)

I have just tried the same formula in googlesheets, and the formula still isn't working, but it seems to be for a different reason

​

=sumifs(Graph!$I$15:$P$15,Graph!$I$14:$P$14,">="&N3,Graph!$I$14:$P$14,"<="&eomonth(N3,0))

This is the only thing stopping me from making big progress at work, so I hope someone would be able to get this formula working! Or if you could think of an easier way, that would be great.

I have around 200 graphs and around 15 mrp sheets which I will be linking, and the graphs will not always be open, therefore I would need the data to display on the MRP, even with the graph spreadsheet closed.

Hope thsi is possible..

Thank you in advance!

 

[sunnyland]

Hello there,

Not sure if this is what you need but if you put this formula
=INDEX(Graph!$E$15:$P$15,MATCH(DATE(YEAR(L3),MONTH(L3),1),Graph!$E$14:$P$14,0))

in L5 of MRP, it may produce the wanted result and it can be dragged to the right. For dragging down I am not sure as I don't know what data you are using from your sample spreadsheet. But not sure if this is what you mean. The hardest thing is to understand exactly what exactly is needed.
Let me know if I understood correctly