I'm trying to combine a number of seating charts (scattered across different workbooks) into one worksheet. My compilation worksheet is set up with times running down column B (9am - 7:00pm in 30 min increments), and each section/row/seat running across row 4. I'm trying to pull the cell color that corresponds to the seat in the seating chart over the course of the day.
Each 30 minute block is housed in its own workbook, and each section is housed in its own worksheet. Each section is laid out like this:
As you can see, each row is of varying length. I've figured out how to pull the cell color, using the Get.Cell function in Name Manager to pull the color codes. However, because the rows in the seating chart are offset, I'm having to create a new name range for every row.
By my count I'd need to create roughly 10,000 names in Name Manager using this method to compile all the information, one name for each row and unique time block. I'm not sure Excel can handle that, but even if it can, I don't think I can. I've tried to create a name in Name Manager for row A, then use the OFFSET function in combination with Name Manager, but it's not working. My current formula looks like =OFFSET(Get_Color_100_AA_0900,1,1,1,24).
Hopefully this makes sense. Does anyone have any suggestions, or is there a more efficient way to go about this problem? Thanks for your assistance.
Each 30 minute block is housed in its own workbook, and each section is housed in its own worksheet. Each section is laid out like this:
| Seat 9 | Seat 8 | Seat 7 | Seat 6 | Seat 5 | Seat 4 | Seat 3 | Seat 2 | Seat 1 | |
| Row A | Yellow | Blue | Yellow | Yellow | Yellow | Yellow | Yellow | Yellow | Yellow |
| Row B | Yellow | Blue | Blue | Yellow | Yellow | Blue | Blue | ||
| Row C | Blue | Yellow | Blue | Blue | Blue | Yellow |
As you can see, each row is of varying length. I've figured out how to pull the cell color, using the Get.Cell function in Name Manager to pull the color codes. However, because the rows in the seating chart are offset, I'm having to create a new name range for every row.
By my count I'd need to create roughly 10,000 names in Name Manager using this method to compile all the information, one name for each row and unique time block. I'm not sure Excel can handle that, but even if it can, I don't think I can. I've tried to create a name in Name Manager for row A, then use the OFFSET function in combination with Name Manager, but it's not working. My current formula looks like =OFFSET(Get_Color_100_AA_0900,1,1,1,24).
Hopefully this makes sense. Does anyone have any suggestions, or is there a more efficient way to go about this problem? Thanks for your assistance.
