Q: How to populate cell with list of values from other cells
Hi everyone,
I have an interesting challenge with Numbers on Mac that I hope someone can help me with. I am trying to auto-populate the cells of a "calendar" table with a list of information provided by a set of "schedule" cells on another table. Let me explain.
Say I have a "schedule" table that shows this:
Then, I have a "calendar" table on a different sheet that shows this:
In this case, I wrote an "IF" formula in the "calendar" table that pulls from the "schedule" table the first three cells and final cell based on the "T" and "2-2:50" cells. The formula looks like this:
IF( Fall 2016::G&Fall 2016::H="T"&"2-2:50", Fall 2016::A &" "& Fall 2016::B &" "& Fall 2016::C &" "& "-" &" "& Fall 2016::N)
The trouble I run into is when there are multiple cells in the "schedule" table that have "T" and "2-2:50" (for reference, these are day/time cells). Let's say there are two, like this:
What I would like to happen is that a list of all values is created in the one cell, like this:
Instead, I get an "invalid reference" error using the formula above. I'm sure this is because Numbers doesn't know how to separate the items, but I don't know a formula command that will do this.
Now, let me address some questions I'm sure someone will ask.
1. Why do I want to do this is Numbers? Because I have to submit the "schedule" table as an Excel file to others where I work. There is no way around this. So, I need to work within this system.
2. Why don't you simply reference the specific cells instead of referencing an entire column? The example above is greatly simplified and quickly done. In truth, there are more than 100 rows and the day/time cells appear randomly in the list. So, on the real thing, there might be 20 other rows between these two and they may change over time. I would think it better to tell Numbers to just pull the required value wherever it is found in this column. But, clearly, the formula I have doesn't do that.
I think that's all. Any help with this would be greatly appreciated!
MacBook Pro, OS X El Capitan (10.11.6)
Posted on Aug 28, 2016 2:59 PM






