COLLECT date values from another sheet

08/31/20
Accepted

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.

Tags:

Best Answers

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Answer ✓

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

    thinkspi.com

Answers

  • Aliaksandr IlyukAliaksandr Ilyuk ✭✭✭✭

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

  • Aliaksandr IlyukAliaksandr 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?

  • Aliaksandr IlyukAliaksandr 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 NewcomePaul Newcome ✭✭✭✭✭
    Answer ✓

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

    thinkspi.com

  • Aliaksandr IlyukAliaksandr Ilyuk ✭✭✭✭

    now everything works, thanx a lot

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Happy to help. 👍️

    thinkspi.com

Sign In or Register to comment.