COLLECT date values from another sheet

Aliaksandr Ilyuk

There are 2 sheets with the same set of columns:

Code / Crop / Start / End / Cost

Start and End Columns are dates.

1) I need to collect date from column Start which satisfies the conditions Code = 1122, Crop = Apple. The formula is the following: =COLLECT({Sheet1 Range 1}, {Sheet1 Range 2}, @cell = Code@row, {Sheet1 Range 3}, @cell = Crop@row), where Range 1 = Start:Start, Range 2 = Code:Code, Range 3 = Crop:Crop.

Why it results #DATE EXPECTED?

2) I need to collect values from column Cost which satisfies the conditions Code = 1122, Crop = Apple. The formula is the following: =COLLECT({Sheet1 Range 4}, {Sheet1 Range 2}, @cell = Code@row, {Sheet1 Range 3}, @cell = Crop@row), where Range 4 = Cost:Cost, Range 2 = Code:Code, Range 3 = Crop:Crop.

Why it results #INVALID COLUMN VALUE?

Thank you in advance.

Paul Newcome

Try using an INDEX function.

=INDEX(COLLECT(...............), 1)

Paul Newcome

Sorting should not affect the formula. When you are creating your cross sheet references to look at entire columns, are you selecting the column header?

Paul Newcome

That would be why. If you are referencing an entire column you should be selecting the column header.

Paul Newcome

Try using an INDEX function.

=INDEX(COLLECT(...............), 1)

Aliaksandr Ilyuk

Everything works great until I change row sorting in the source table, then an error message appears (#INVALID VALUE)

Aliaksandr Ilyuk

In case of a more complex table with parent rows, how to set up a reference range only for children rows or for the whole column including parent and children rows?

Paul Newcome

Sorting should not affect the formula. When you are creating your cross sheet references to look at entire columns, are you selecting the column header?

Aliaksandr Ilyuk

But it affects. I don't select the column header, just a range of cells. After rows sorting the previously predetermined range in the reference window shrinks.

Range before sorting Start1 : Start12

Range after sorting Start3 : Start10

Paul Newcome

That would be why. If you are referencing an entire column you should be selecting the column header.

Aliaksandr Ilyuk

now everything works, thanx a lot

Paul Newcome

Happy to help. 👍️