RESOLVED issue: TRANSPOSE function of Excel is not working (try the alternative C+S of the keyboard)

[wendy20]

Hi there,

I have encountered an issue with an excel array function using a Windows computer.

I am sure the formula and items in the function are all used correctly. I can replicate the same order when doing the exact same thing using my Macbook.

The excel always show #value (error value) when I hit C+S+E. I don't know why this shortcut key just doesn't work in excel of this computer.

Please help!! 

THANK SO MUCH EVERYONE!!

[#2]

@wendy20 Not sure if this helps, but I replicated your schedule and used both CSE and without (current MS365 version), without problems. Tested on both PC (Windows 10) and Macbook (Catalina).

As per the help pages, a #VALUE! error will occur when any of the cells are empty or contain text, or when the number of columns in array 1 is different from the number of row in array 2. You don't seem to have empty cells and the arrays are sized correctly. So, are you sure that all cells in both arrays are numbers?

Attached my file. See if it works on either of your machines.

[#3]

@Riny_van_Eekelen 

Hi Riny,

Thank you so much for helping me out with those efforts!

Yes, I am sure that all the cells in both arrays are numbers. 

I have downloaded the file you've attached and tried to redo your working.

1. Cleared the existing content

2. Selected the right cells

3. Typed in the formula required 

4. Tried to C+S+E      *********The excel seems to be frozen every time when I hit these buttons. No response at all.

So I went back to trace the error. As shown in the evaluation of calculation, the first step on the TRANSPOSE(B10:G11) has already resulted in an error value.

I am suspecting that there are some issues with my Excel setting. What do you think?

Anyways, I really do appreciate you for taking your time and energy to help me.

Cheers! 

[#4]

@wendy20 But did it work OK before you started redoing my formulae? Not sure why you get the value error. According to the help screens that will occur if you do NOT enter the formula with C-S-E where your Excel version requires it. You tagged your original post with "Office365". If that means that you are a MS365 subscriber using a current version of Excel, you wouldn't have to worry about C-S-E.

[#5]

@Riny_van_Eekelen 

Yes, I can see the results of your working perfectly fine.

I just can't edit anything of the formula.

I believe I have the Office 365 Subscription.  I tried to do it again by just hitting only E. The alert of ' you can't change part of an array' always pops out. 

[#6]

wrong assumption, I don't have the subscription to Office 365, mine is 2019 for Family and student @wendy20 

[#7]

@wendy20 Since you are not on MS365, try entering the TRANSPOSE portion of the formula only and hit C-S-E.

[#8]

@Riny_van_Eekelen 

Same as before, C+S+E, no response from excel. 

[#9]

@wendy20 Then I'm lost. Sorry!

[#10]

The formula in cell b123 is not an array ( CSE ) formula, otherwise Excel must enclose formula with { } braces/curly brackets. Please upload the file you are using and let us examine it. Once move to b123 press F2 and hit Ctrl+Shift+Enter simultaneously. 

[#11]

@Rajesh_Sinha 

Hi Rajesh,

I have figured that the issue is not about my input, rather it is my excel's functionality on TRANSPOSE.

It simply wouldn't allow me to transpose anything. The excel just left hanging whenever I hit C+S+E.

Thanks for asking!

[#12]

Thanks for the help so far! @Riny_van_Eekelen 

[#13]

@wendy20 

Can you even replicate the arrays

{= B120:G121}

using CSE?  A value error will arise if you enter the formula without CSE (ignoring 365 for the moment) because implicit intersection does not return a unique cell for the formula.

It could be that the failure to commit the formula is one of 'finger trouble'.

Are you holding Ctrl+Shift then clicking the Enter key?

[#14]

@PeterBartholomew1 

Hi Peter,

Nope, I can't replicate {= B120:G121}.

Don't think its a finger trouble. I was holding the C+S keys before I hit E.

Thanks.

[#15]

@wendy20 

Please do not feel insulted by my suggestion.

Perhaps an Office repair is called for?

[#16]

@PeterBartholomew1 

Called for an online repair.

Problem resolved when I tried to hit the other set of CS +E on the keyboard. What a joke.

Thank you so much!!

[#17]

@wendy20 

Just for my interest, what do you mean under another set of Ctrl, Shift and Enter buttons?

[#18]

c and s on the left-hand side of the keyboard

@SergeiBaklan 

[#19]

@wendy20 

If under c and s you mean Ctrl and Shift, they shall work the same if you use left or right side of the keyboard, at least for Ctrl+Shift+Enter

[#20]

Nope, the C+S on the other side of the keyboard just wont work @SergeiBaklan