Problems with an array formula...

[Z_Sutcliffe123]

I have 2 sheets on my workbook. Sheet 1 has all the master data. I'm using Sheet 2 to pull data from Sheet 1.


Sheet 1 has the date in Column A, that date is converted to month text in Column B and in Column C I have the corresponding data for those dates - in this case a revenue number.


On Sheet 2 I have a dropdown list with the month names in (they definitely match the names in Column B on Sheet 1) in cell A2. I'm trying to pull in the corresponding revenue numbers from Column C on Sheet 1 when I select a certain month in the dropdown.


At the moment my array formula seems to be working but for one problem. I have copied it down 31 cells on Sheet 2 to account for the months with those most days. However, in months with fewer days like February it's displaying 3 days of data from March in the bottom 3 cells. Is there anyway to stop it including these March days? I've got no idea why it's happening but I suspect it's some error I've made with the COUNTIF part of the formula.


This is the formula I'm using:


=INDEX('Sheet1'!C1:C3000, MATCH(0, IF($A$2='Sheet1'!$B:$B,COUNTIF($B$2:$B$2,‘Sheet1’!$C:$C),""), 0))

Any help would be greatly appreciated.

 

[steve the fish]

Im unclear what you are trying to do. What is in Sheet2? Do you want the sum of each day or each month?

 

[Z_Sutcliffe123]

I'd like to get the data from Column C on Sheet 1 for each day in the corresponding month

 

[steve the fish]

Sure. What do you mean by 'get'? Get the first value? Sum all of the values? Bring all of the rows over? Like i say its unclear.

 

[Z_Sutcliffe123]

So if I choose Feb-19 from the drop down it shows the data from Column C on Sheet 1 for each day in Feb-19

 

[steve the fish]

Ok so how is Sheet2 set up? You have the month in A2. What else?

 

[steve the fish]

Before go any further what do these produce:

=ISNUMBER(Sheet1!B2)
=ISNUMBER(Sheet2!B2)

where both are pointing at the textual month in each sheet (eg Feb-19). Just need to check comparing like with like.

 

[Z_Sutcliffe123]

So Sheet1 has data for every day of the year in Column C, date in column A and month name in Column B. I want to use the dropdown on Sheet 2 to pull in all the cells in Column C (Sheet 1) for each day of the month.

Sheet 2 only contains a dropdown which is in the cell, A2.

To make things slightly clearer here are two screenshots.

1) This is sheet 1:
https://gyazo.com/ae77085db89e918672b4bc22656aae1a

2) This is sheet 2:
https://gyazo.com/ec5dd3e142ecac05734953992c40e616

 

[steve the fish]

You can do it in one cell like this:

=IF(ROWS($A$1:A1)<=DAY(EOMONTH(0+(1&$B$2),0)),INDEX(Sheet1!$D$2:$D$1000,MATCH(0+(ROWS($A$1:A1)&$B$2),Sheet1!$A$2:$A$1000,0)),"")

however id put a column of dates in corresponding to the month then use them. Let me know if you want to use that route or what i have given is ok.