*** SOLVED *** convert row to column - this is easy - transpose -- BUT --

[TheeeDragon]

situation:
column headings = 1 to 120 (for 120 months)
then a few rows down - calculating the interest per month (easy)
but I want to table on sheet 2 to look like (eg):

month interest
1 11
2 13
3 15
... ...
120 99

this is EASY, BUT
- it works the FIRST time (as the paste transpose copies a VALUE)

BUT

If I update some basic info on sheet 1, which CHANGES the CALCULATED interest
the interest cells (shown in the table above) will NOT auto-update - I have to do it by hand - argh
(ie recopy the row and repaste via transpose to get the new values

so.... how can I make the column auto-update???

THANKS!

[oeldere]

use VBA for that.

Or use Power Query for that.

If you post a excel file, I will show it to you in your file.

[ChemistB]

If your data is in row 2, A:CZ (for example), you can use this formula in Sheet2

Please Login or Register  to view this content.

copied down

[TheeeDragon]

been using excel for 30 years
1. never tried any VBA
2. never heard of Power Query -- just looked it up = WOW
3. will post a sample file in few minutes
thanks!

[TheeeDragon]

this works ==

=INDEX(Sheet1!$D$42:$L$42, ROWS($A$1:$A1))

have no idea WHY?
but YAHOO == thanks!

this will help me often

[TheeeDragon]

here is a dummy file
** removed == see next post
thanks

[TheeeDragon]

just saw the guidelines
made a smaller test file