Populating various cells in a range based on a table of cell addresses

How about this?
The table solution is a bit more flexible.

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

Sorry, I missed that you are on Excel 2003, the table solution will not work there.

As for the Named Range solution it works perfectly well here and I don't see any reason it shouldn't work on 2003.
The other addresses are outside the view to the left, did you scroll far enough to the left to see them?

Hmm... at closer inspection the addresses are surrounded by spaces. The reason for this is that I copied them from your first post and then used Text to Columns to separate them. But then again, Y11 was also surrounded by spaces.

Here is a new version to try, all spaces removed and all results within visible range.

Hi Jacc,

Just getting back to this - thanks. This looks like it works. However, I have been thinking about this some more and fear that my setup here may have been over-complicated. I've attached what I think is a simplified setup (attached sheet, rows 3-11), which shows the desired outcome (rows 14-22). In summary:

1. I've got an input table (shown on attachment in columns B-E) where users enter event (i.e. launch step 1, launch step 2, etc.) dates (the dates they enter are sequential calendar quarters starting in January, 2013 (that would be Q1) for a list of products

2. On another sheet (shown in the attachment on the same sheet in columns G-P for simplicity), there is ths same list of products, with a number of rows associated with each. I'd like one of the rows associated with each product to be populated with the event names, in the cells that correspond to the dates for those events, as entered in the table. For example, "Launch step 2" for product #2 was entered as taking place in calendar quarter 6 (Q2 of the second year), so I'd like the cell in the "Product 2 Launch" line, in the Q6 column to be populated with the event label "Step 2" as indicated in the table.

My original approach involved converting the date entries to cell references, but it seems that maybe I can simplify by just referencing the cells based on their column number (quarter number), as per my example.

Does this make sense? Any suggestions? Note that I'm working in Excel 2003. Thanks in advance.

Here are some solutions, I made an arrayformula one as well just for kicks. I can't say I'm really happy with either one but I think the array formula one may actually be easier to work with.

The reason I'm not happy with either solution is that I feel that both algorithms are pretty clumsy. If anyone wants to improve them you are more than welcome!

This one is no less clumsy but at least it's more generic. It's easier to add columns.

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.