TRANSPOSE function returns incorrect value when array has single value

[bpeikes]

Using Excel 2010. I have a table, let's call it Table_Items, which has a column called Id. Its on the second sheet of a workbook. On the first sheet of the workbook I'd like to display the values from the Id column transposed across the sheet. I know that I won't have more than 10 items in Table_Items.

I select a range going from Column A on the first sheet all the way to column J and put in the formula
= TRANSPOSE(Table_Items[Id])
and hit Ctrl-Shift Return.

When Table_Items has more than one row, it seems to work fine. For instance if Table_Items looks like this:

Id	Quantity	Value
ABC	3	2
BDF	1	4

I'll see the following:

ABC

BDF

#NA

#NA

#NA

#NA

#NA

#NA

#NA

#NA

The problem is when there is only one row in the table, i.e.:

Id	Quantity	Value
ABC	3	2

I see on the first sheet:

ABC

ABC

ABC

ABC

ABC

ABC

ABC

ABC

ABC

ABC

Any ideas on why Excel does this or how to get around it? I can post an example if needed but I think it can be easily reproduced.

[dilipandey]

Hi bpeikes,

Try putting value or removing values from row 3 in the attached file and let me know if this helps...

transpose.xlsx

Regards,
DILIPandey

<click on below 'star' if this helps>