Table to List Data Formatting Problem.

Hello,

I’m trying to convert several fairly small tables into lists using formulas but am having a problem with the number/text format resorting to default, when it’s made into the list. The list part is working, it’s just the formatting that changes.

I've attached an example file.

The data in the tables could be currency, dates, percentages or any other format and I need them to be copied into the list’s cells with the same number/text format appearance. Eg, If D11 has the date 21/12/2010, and in O17 I then enter =(D11), O17 will also have the 21/12/2010 date.

As I am referencing cells indirectly, I think it’s this that’s causing me the problem. At the moment I’m using the INDIRECT function but have also experimented with INDEX. Both haven’t formatted the data correctly.

My tables’ column/row size varies but I input each table size in 2 separate cells to make cell referencing a little easier (eg Rows 9, Columns 5) and the tables always start at B10. Doing it this way, I can get the formula list to count up for the columns & rows.

I then use these column and row numbers to reference the cells in the table.

The other problem with the INDEX function is that I don’t know how to input the range as Column numbers and Row numbers.

Has anyone got any ideas how I could keep the formatting?

Also and although it still wouldn’t format, how would I enter the range in the INDEX function using Row numbers and Column numbers?
Thanks.

Thanks for your reply. It was the $B$10:$F$26 range part of the INDEX formula which I’m unsure of how to input as columns & rows. Eg The above range would be Column(2)Row(10) to Column(6)Row(26). How would I enter that range into the INDEX formula in place of $B$10:$F$26?

The only reason I want to know this is so that the formula will read any table of any row/column length, by using just the two numbers I enter. For example I enter; Number of Columns 6 & Number of Rows 40. The range would then be similar to INDEX($B10:Column(6)Row(40).

The rest of your formula has saved me the trouble of trying to shrink mine later on and even then, it wouldn’t have been anywhere as neat. So thank you for that.

I also take it that there is not much chance of finding another method to copy the number/text format over to the list?

Ok. Thanks for trying to help and thanks for the formula.