Query formula greyed out

[ozbeachbum]

Hi,
I am using the following formula
=FILTER(Data!$GN$70:$GN$340,$H$70:$H$340=Data!$GD70,"")
The destInation range is J70:J340 each even numbered row has the formula, each odd numbered row is blank.
For some weird reason the formula is repeated in the following odd numbered rows 87 89 91 93 95 147 157 163 169 171 195 but it is greyed out.
Wondering what the reason might be and if there is a way to correct it.
Cheers,
Dave.

 

[Peter_SSs]

Pretty hard to be sure without any sample data. Generally a greyed out formula means the cell is part of a spill range. For example, if you select the formula in J86 (where I presume the formula is not greyed out?) do you see a blue outline around (at least) J86 and J87?

 

[ozbeachbum]

Hi Peter,
Had the same thought as you mentioned re without data, I could send a mini sheet if you think that would help, I did not do originally as the formula is referencing another worksheet and wasn't sure if it would be helpful.
The answer to your question is yes.
Weird though that it is not consistent through the range.
Thanks for the reply.
Cheers,
Dave.

 

[RoryA]

That just means that some of the formulas are only returning one row, and others 2, which means you have multiple matches for some values in column DG on Data, and not others.

 

[ozbeachbum]

Thanks RoryA for the explanation.

 

[Peter_SSs]

Wondering ... if there is a way to correct it.

If the second row of those situations is always blank, or you only want the first value anyway, you could change your formula to the following to stop that spill into the next row

=INDEX(FILTER(Data!$GN$70:$GN$340,$H$70:$H$340=Data!$GD70,""),1)

 

[ozbeachbum]

Hi Peter,
Thanks as always, works great.
Dave.

 

[Peter_SSs]

Cheers.