Converting Matrix Chart to a 3-column table

[alexx579]

Hi Guys,

I have attached a file of what I am looking at. Here is my one problem, in cell P1, I want to be a column heading....but if I move the table down one row and double click to apply apply formula to lowers rows, "Area 1" will only show 10 times in stead of 11. Is it a simple fix of the formula?? Any help is appreciated.

Thanks,A!!!

[Pete_UK]

Instead of using OFFSET, which is a volatile function, I would suggest these formulae:

T1: =INDEX(A:A,INT((ROWS($1:1)-1)/11)+2)

U1: =INDEX($B$1:$L$1,MOD(ROWS($1:1)-1,11)+1)

V1: =INDEX($B:$L,INT((ROWS($1:1)-1)/11)+2,MOD(ROWS($1:1)-1,11)+1)

You can put IFERROR( ... ,"") around them if you wish, and you can put them in P1, R1 and Q1 (I put them in T1:V1 to compare against yours). Then you can insert as many rows as you wish above them (or put them directly into row 2).

Hope this helps.

Pete

[alexx579]

Hi Pete, instead of hard coding "11" columns....is there a substitute in case more are added, the 3-column table will update automatically?

[Pete_UK]

You can change the 11 (which occurs 4 times in the above formulae) to this, for example:

COUNTA($B$1:$O$1)

so that if any more columns are inserted within that range then it will automatically adjust and count the number of headings that are set up.

Hope this helps.

Pete

[alexx579]

Thanks Pete! I will give it a try!!

[Pete_UK]

You're welcome.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

[alexx579]

Done! Thanks again!