Been using excel 5 years yet I can't figure out what should be an easy problem.

Hi there, TIA for the help. I'm using excel 2010.

I want to create a table that expands horizontally, not vertically. The limitation of "no formulas in the table header" is frustrating the daylight out of me.

I've accomplished most of the following:

1. Build a table object in excel that grows horizontally by one column each month (regular monthly report)
2. Connect a chart to the excel table where the heading of the table is the x axis, and the data in the chart points to one or more rows in the table
3. Make sure the chart is "Sticky" so that when the table expands, so does the chart
4. Run my update macro that expands the table one column left to right
5. When the macro expands the table, all the formulas in each row expand with it, and in addition have the header row increment by one month

Main problem:

The headings of the table are automatically formatted to text. I can't think of a single reason why they would do this. When I expand the table, I need the heading (which is formatted as a simple Month/Year like "Jan-13") to increment up by one month. Typically I use the following formula to achieve this outside of the table object:

=DATE(YEAR(A1),MONTH(A1)+1,1)

I can't use that formula in the header row of a table though.

How the hell do I accomplish this?

Thanks!!!
-Tim

Give up on using tables, or transpose your data.

Excel is very much row-centric.

What's the obstacle to transposing the table?

Hi Tim,

We would love to continue to help you with your query, but first, before we can proceed, please see our Forum Rules #1 about proper thread titles and adjust accordingly...

• To change a Title on your post, click EDIT then Go Advanced.
• If 2 days have passed, ask a moderator to do it for you.