Excel 2007 : How do I dynamically lookup a cell that is based upon another cell which was dynamica

[ccgaines]

How do I dynamically lookup a cell that is based upon another cell which was dynamically referenced?

I have 2 tabs. The first tab has all my data and 5 calendar weeks, all of which change each month.

The second tab has the entire year in a table array, with the dates at the top, from $B$1:$ABC$1.

Each date is merged into two, horizontally positioned cells (two columns). I have already figured out how to dynamically return the column directly under the referenced date, but need help returning the column to its immediate right. The formula I am looking for would be placed in the column shaded red on the first tab.

Any thoughts or suggestions would be greatly appreciated!