James Minor 3

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:

Screen Shot 2016-08-28 at 5.33.38 PM.png

Then, I have a "calendar" table on a different sheet that shows this:

Screen Shot 2016-08-28 at 5.36.53 PM.png

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:

Screen Shot 2016-08-28 at 5.52.02 PM.png

What I would like to happen is that a list of all values is created in the one cell, like this:

Screen Shot 2016-08-28 at 5.44.04 PM.png

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

Close

Q: How to populate cell with list of values from other cells

  • All replies
  • Helpful answers

  • by Wayne Contello,

    Wayne Contello Wayne Contello Aug 29, 2016 6:29 AM in response to James Minor 3
    Level 6 (19,017 points)
    iWork
    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. 

  • by t quinn,

    t quinn t quinn Aug 29, 2016 8:22 AM in response to James Minor 3
    Level 5 (5,002 points)
    Mac OS X
    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

  • by James Minor 3,

    James Minor 3 James Minor 3 Aug 29, 2016 6:08 PM in response to t quinn
    Level 1 (24 points)
    iWork
    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:

    Screen Shot 2016-08-29 at 9.07.43 PM.png

  • by t quinn,

    t quinn t quinn Aug 29, 2016 8:22 PM in response to James Minor 3
    Level 5 (5,002 points)
    Mac OS X
    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.

    Screen Shot 2016-08-29 at 8.55.57 PM.png

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

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

    Screen Shot 2016-08-29 at 9.01.45 PM.png

    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.

    Screen Shot 2016-08-29 at 9.03.49 PM.png

    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

  • by James Minor 3,

    James Minor 3 James Minor 3 Aug 30, 2016 6:01 PM in response to t quinn
    Level 1 (24 points)
    iWork
    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.