Search value in an array of numbers and return

Here is another possibility for you.

What makes your problem difficult is the arrangement of your table with multiple (and an unknown number) of columns for Sow, Plant, Harvest. If it was arranged so that there was one column of each, this would be a much more simple problem. So that is what i have done. The first screenshot is how it would look with categories turned on, a few columns hidden, and some other formatting. The second is the set up. There are a lot of blank columns in Table 1 because I was trying to match it up with your columns but I added a new column B in my solution so I am actually off by one column.

Table 1 column B is used to indicate which planting it is for the crop.

Table 1::L4 formula =$A4&" "&B4

Fill down to complete the column

Table 2::A2 is the date entry

Table 2::B2 =WEEKNUM(A2,1)

Table 2::F1 through H1 have the number of header rows in Table 1. It looks like you have 3 headers.

Table 2::F2 =MATCH($B$2,OFFSET(Table 1::I$1,F1,0,ROWS(Table 1::$A)−F1,1),0)+F1

Fill across to H2 then fill down with all

Table 2::C2 =IFERROR(INDEX(Table 1::$A,F2)&" "&INDEX(Table 1::$B,F2),"")

Fill across to E2 then fill down with all

I hid a few columns and set up Table 1::A as a category. I did not format for your color scheme but that is easy enough to do.

The formulas in F,G,H find the row numbers in Table 1 that match the week number. The formulas in C,D,E return the crop name and planting number from Table 1

When using it, add enough rows to Table 2 so that the last row is blank for Sow, Plant and Harvest. This will ensure all crops are listed.

parentheses_ wrote:

search the array of succession numbers and return all vegetable names (left column) that match the week of the year, based on the current week.

Just in case you are not already aware Numbers has powerful built-in filtering capability designed to do just this sort of thing.

Here is an introductory description:

Filter data in Numbers on Mac - Apple Support

If you want to see data for a particular week you can filter on column B.

SG

I think you are saying you want a list of vegetables, separated by commas, perhaps, like in a long sentence.

That's not impossible but rather fussy to program. How about this...

Reformat your Sow, Plant and Harvest columns to plain white or clear backgrounds. Then Conditionally Format the columns to color-code the values in the columns. Up to me, I would cause all the week numbers that match the current week to have a GREEN, background. Now you could just visually scan down the column to find the date matches and read across to the vegetable name. Each time the current week number changes, the highlighting will update.

If you wanted to make a list, you could Sort the column that you are interested in, say the Sow column. That would group all the highlighted cells somewhere in that column. Then you could Select the group of Vegetable names with the highlighted week dates, copy, and paste into your To Do List.

Sorting may or may not work on your Table Columns depending on whether you have merged any cells, or if you have column labels in Non-Header rows. If you have trouble with any of that, I'd be glad to help.

You could put everything back into the original order by sorting on your Vegetable name column.

Jerry

Just thinking about how this huge table could be simplified. Would it make sense to group all the Sow columns into one table, all the Plant columns into another, and Harvest columns into a third? These tables would be separate from your first eight columns of check boxes, constants and calculations. That would simplify finding all the matches for each activity because every cell in a given row would be involved in the activity. A summary table could have as few as four columns; A - The list of vegetables, B - A signal that a date match was found somewhere in that vegetable's row for Sow, C - A signal that a date match was found somewhere in that vegetable's row for Plant, and D - A signal that a date match was found somewhere in that vegetable's row for Harvest.

The Sow, Plant and Harvest tables could be on a separate Sheet or Sheets to reduce the density on the main sheet.

Regards,

Jerry

Badunit,

Since Paren separated Sow, Plant and Harvest dates into separate tables, there's no problem finding occurrences of the Current and Next week numbers. A simple =Countif(row:row, week number) will return a 0 or a 1 to indicate a hit, no matter how wide the table is.

Jerry

Paren,

Here's the Autosort/Compress solution I mentioned above. Sorry if it's a little busy with the notes and expressions. Happy to elaborate if needed.

Just a couple of comments...

The thought here is that in the large cell where you wanted a list of the week's Sowings you can overlay a Table which I called "This week compressed" here, obviously you would change that. Or, you could just build the compressed table into your larger table. To program this list, I added two columns to your "This Week" table in order to create a numbered list of vegetables. In "Row", I calculate the number of the row the vegetable is listed in, and I give the blank cells the arbitrary high number "999". The row number then is used to "rank" the visible vegetables based on their order in the table in the "Rank" column. In the compressed table I use the LOOKUP function to find the vegetable that is "ranked" a value equal to the row number-1 in the compressed list.

Jerry

P,

I continue to think about getting to your stated goal. Saturday evening, I had this:

On Sunday morning I have this:

I guess that shows the the week number calculation works!

What you see is my doodling around the idea of creating a list of items that meet a week-number criteria.

Thinking about where to go next, I have to ask how many columns are there in the "successions" region?

Regards,

Jerry

Again, please tell me more about how wide your Table is, in columns. It seems to go on forever in your screen shots.

I'm still thinking about how I could come closer to your preferred layout.

Regards,

Jerry

Paren,

I've been away for about a week and you've made great progress. The last table you posted, Week by Week, is rather messy, as I imagine you would agree. In an answer I posted a week ago or so, I wrote an automatic sort solution that might be useful here. I'll see what it would take to adapt it.

Jerry

P,

You've done a great job of giving us the lay of the land. That's a big help.

My first question for you is what does "Return all vegetables under any..." look like?

Jerry