How to populate cell with list of values from other cells

Question

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

Reply

Answer 1

Aug 29, 2016 6:29 AM in response to James Minor 3

I suggest posting a larger input data set AND an actual desired output.

Reply

Answer 2

t quinn

Level 5

7,063 points

Aug 29, 2016 8:22 AM in response to James Minor 3

Hi James,

It is possible to get the info you want into a single cell into your calendar table. I would create an index column in your scheduling table to do this. We would need to know more about the data you are drawing from. What are the other possible values in this cell? Is L.Smith the only one likly to show up on T @ 2?

quinn

Reply

Answer 3

Aug 29, 2016 6:08 PM in response to t quinn

Hi Quinn,

No, that is not the only one. Basically, you're looking at a college course schedule, so, again, there might be more than 100 of these, with a dozen different pieces of info. I think that's why I'm aiming for a way to have Numbers search a broad, random area--I can't guarantee these cells will be the same every time. I can guarantee the columns, which you can see. So, the day, like "T," will always appear in that column. The instructor will always appear where you see "L. Smith" and so on.

I'm currently manually creating/editing the calendar based on the schedule. The issue is whenever there is a change, I have to remember to change the calendar as well. It gets to be quite a pain, thus my hope to automate this. The calendar is more for me, so I can see who is where and when.

Here's a larger example of the schedule list:

Reply

Answer 4

t quinn

Level 5

7,063 points

Aug 29, 2016 8:22 PM in response to James Minor 3

Hi James,

Here is an approach that requires additional columns in your scheduling table. Columns L-R can be hidden. I needed to simplify (for Numbers) the day column. This involved making a column for each day. I think it is as readable as the single column.

L2= CONCATENATE(A2," ",B2," ",C2," ",K2)

it is filled down. "summary" is the column that will move to your calendar.

M2= COUNTIF(D$2:D2,TRUE)&IF(D2,D$1&$J2,"")

This is filled down and across. It builds an index for each day of the week.

Here is your calendar.

B10= INDEX(Table 1::$L,MATCH(ROW(cell)−9&B$1&$A10,Table 1::M,0),)

This is filled down in all the 2-2:50 rows.

B6= INDEX(Table 1::$L,MATCH(ROW(cell)−5&B$1&$A6,Table 1::M,0))

This is filled down to all the 11-12:15 rows.

B2= INDEX(Table 1::$L,MATCH(ROW(cell)−1&B$1&$A2,Table 1::M,0),)

If you need to have more or less time rows (say you have more than 4 classes taught at 11) you will need to adjust the formulas where you find this

ROW(cell)−1

ROW(cell)−5

ROW(cell)−9

The value of that part should equal 1 for the first entry of that time.

To make this pretty I would wrap these functions in IFERROR() after I got everything working. You could then run a filter to hide some of the blank rows.

Let me know if you have any questions.

quinn

Reply

Answer 5

Aug 30, 2016 6:01 PM in response to t quinn

Quinn,

A quick note to say thanks for this code. I'm in the middle of finalizing the schedule, so it's going to be a few days before I can study this and try it. I will certainly come back and let you know the results.

Reply