COLLECT date values from another sheet
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 = [email protected], {Sheet1 Range 3}, @cell = [email protected]), 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 = [email protected], {Sheet1 Range 3}, @cell = [email protected]), where Range 4 = Cost:Cost, Range 2 = Code:Code, Range 3 = Crop:Crop.
Why it results #INVALID COLUMN VALUE?
Thank you in advance.
Best Answers
-
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?
thinkspi.com
5 -
Paul Newcome ✭✭✭✭✭
That would be why. If you are referencing an entire column you should be selecting the column header.
thinkspi.com
1
Answers
Try using an INDEX function.
=INDEX(COLLECT(...............), 1)
thinkspi.com
Everything works great until I change row sorting in the source table, then an error message appears (#INVALID VALUE)
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?
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?
thinkspi.com
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
That would be why. If you are referencing an entire column you should be selecting the column header.
thinkspi.com
now everything works, thanx a lot
Happy to help. 👍️
thinkspi.com