Copy values from horizontal to vertical

[jrlafrance]

I come bearing gifts in the form of Excel questions, once again!

This time, I have totals from columns in a horizontal row (B53:AA53), on a sheet (Sheet1). What I would like to do is automatically have those values carry over to my other sheet (Unit Totals) which is listed vertically (C3:C28).
The tricky part is that since the totals from 'Sheet1' are generated from a formula, Excel won't let me copy/paste using the "transpose" format.

Any help would be appreciated! On my spreadsheet, I have over 2600 cells to copy over, this was condensed for an example.

File is attached for your viewing pleasure!

Mahalo
James

[Hikari]

When you paste special > select Value (under paste) & Transpose (under operation) > OK

[Greg M]

Hi there,

The following code will copy the transposed VALUES to the "Unit Totals" worksheet:

Please Login or Register  to view this content.

The highlighted values may be altered to suit your requirements.


Hope this helps - please let me know how you get on.

Regards,

Greg M

[jrlafrance]

Thanks Greg! Works like a charm! Only issue now is how to have the "units total" sheet update the quantities automatically when changes are made in "sheet1"?

[Gregb11]

I don't know what version this came out in, but you should be able to use the Transpose formula. Type this in the cell you want to start the values:

=TRANSPOSE(Sheet1!B53:AA53)

This is a dynamic array formula so as your values change on sheet one, these values will also change.

[Greg M]

Hi again,

Many thanks for your feedback.

If the values displayed on the Unit Totals worksheet are "just for looking at" (i.e. those values are not used for calculations in other worksheets) then a possible cheap 'n' cheerful solution would be to place the following code in the VBA CodeModule of the Unit Totals worksheet (i.e. not in a standard VBA CodeModule).

The result of this will be that the values on the Unit Totals worksheet will NOT be updated every time a value is changed on the Sheet1 worksheet, but whenever a User needs to view the Unit Totals worksheet (s)he will need to activate that worksheet, and the activation event will trigger the routine which updates the transposed values.

Please Login or Register  to view this content.

Hope this helps - as before, please let me know how you get on.

Regards,

Greg M

[jrlafrance]

Thanks Greg! This solution is much easier to work with, thanks for the formula!

[Gregb11]

You're welcome! Glad it worked.