transposing links

[Guest]

I have very large sections of data which I need to transpose as links.
Problem is that the copy/paste links function cannot be followed up with the
copy (the linked cells)/transpose option, without having the transposition
screw the links up. Any way around this?

 

[Guest]

You can write formulas to effectively transpose the data.

Converting vertical data to horizontal:

If you want data in cells A2:A11 to be referenced across columns, use a
formula like this in the first column and then copy it to the other cells
where you want the data to appear.

=OFFSET($A$1,COLUMNS($A:A),0)

Converting horizontal to vertical

Assume your data is in cells B1:K1, use the formula

=OFFSET($A$1,0,ROWS($1:1))

 

[Dave Peterson]

I paste the links in an out of the way spot.

Then I convert the formulas to strings and then copy|paste special|transpose.

Then convert the strings back to formulas.

After you've pasted/built the links:
edit|replace
what: = (equal sign)
with: $$$$$=
replace all

Copy that range and edit|paste special|transpose

Then change them back:
Select the pasted range
edit|replace
what: $$$$$=
with: =
replace all

And clean up that out of the way spot, too.

 

[Guest]

Well that is just sexy. Very nice! As I was reading Duke's I was already
just thinking of another solution, but this is a great idea. Kudos for
creative, and so simple. Nice!

The idea is this...I am doing all this because I have users which are fairly
unsophisticated and need the standard vanilla horizontal inputs that rows
offer. That said, there is a level of analysis I'd like to do which pivots
can help me with. So I am trying to give them an entry sheet, which I can in
the background (separate, hidden sheet) have linked to a pivot style list.
By this, if it's not obvious, I am thinking that I need the individual list
of values, each effectively "described" with the fields that go horiztonally.
So if someone enters 12 months of data on a sheet to show me year's spend, I
convert that to 12 rows, each of which has the same "date" field, just with
different values. Anywho, the point is that as I was reading Dukes, I
realized that that so long as the number of rows that any given row occupies
once converted to vertical (in this example, let's say there are 12 because
date is the only thing, but it would be 24 if I also had two different style
numbers for which they told me the 12 month balances) stay constant (which
they do, since pivots just describe every item and then can just ignore
unneeded ones), all I have to do is make one "model" set of a set of vertical
rows that reaches out to the different horizontally stretching rows (make row
one stretch up and over 1 for Month 1, up and 2 for Month 2, etc.). Then I
would just take that model set and, starting from the bottom (as when you
insert copied cells it pushed everything down, and you can just keep going
upward without the screen getting thrown around), just ctrl-shift insert the
set under every horizontal line I have. At the end, could just autofilter
down to the "long" lines (the ones which at this point would point to the
lines that I want people to be filling in). Grab the long lines and just
shft-drag them to the end of the list. Now I can take the lines that are all
linked to these lines and effectively move them around, maintaining the links
that were created. And no matter where I move them, my user will see the
original, pretty structure. I'll have in the background a named range that I
can use to make flashy stuff with using pivots.

Long-winded response, but not sure how else to describe. But as I think
through it, I don't know that there are any cases where I cannot think of
making this "model" set that the idea of transposing the set would not help
out. Thanks for the effort.

 

[Dave Peterson]

I'm not sure I understand the intricacies of your situation, but I don't like to
use formulas like this--to point directly at a cell.

If I had a choice, I'd try to use =vlookup() or =index(match()).

it seems lots safer.

Debra Dalgleish's has some notes you may like:
http://www.contextures.com/xlFunctions02.html (for =vlookup())
and
http://www.contextures.com/xlFunctions03.html (for =index(match()))

Well that is just sexy. Very nice! As I was reading Duke's I was already
just thinking of another solution, but this is a great idea. Kudos for
creative, and so simple. Nice!

The idea is this...I am doing all this because I have users which are fairly
unsophisticated and need the standard vanilla horizontal inputs that rows
offer. That said, there is a level of analysis I'd like to do which pivots
can help me with. So I am trying to give them an entry sheet, which I can in
the background (separate, hidden sheet) have linked to a pivot style list.
By this, if it's not obvious, I am thinking that I need the individual list
of values, each effectively "described" with the fields that go horiztonally.
So if someone enters 12 months of data on a sheet to show me year's spend, I
convert that to 12 rows, each of which has the same "date" field, just with
different values. Anywho, the point is that as I was reading Dukes, I
realized that that so long as the number of rows that any given row occupies
once converted to vertical (in this example, let's say there are 12 because
date is the only thing, but it would be 24 if I also had two different style
numbers for which they told me the 12 month balances) stay constant (which
they do, since pivots just describe every item and then can just ignore
unneeded ones), all I have to do is make one "model" set of a set of vertical
rows that reaches out to the different horizontally stretching rows (make row
one stretch up and over 1 for Month 1, up and 2 for Month 2, etc.). Then I
would just take that model set and, starting from the bottom (as when you
insert copied cells it pushed everything down, and you can just keep going
upward without the screen getting thrown around), just ctrl-shift insert the
set under every horizontal line I have. At the end, could just autofilter
down to the "long" lines (the ones which at this point would point to the
lines that I want people to be filling in). Grab the long lines and just
shft-drag them to the end of the list. Now I can take the lines that are all
linked to these lines and effectively move them around, maintaining the links
that were created. And no matter where I move them, my user will see the
original, pretty structure. I'll have in the background a named range that I
can use to make flashy stuff with using pivots.

Long-winded response, but not sure how else to describe. But as I think
through it, I don't know that there are any cases where I cannot think of
making this "model" set that the idea of transposing the set would not help
out. Thanks for the effort.

 

[Guest]

vlookup is a difficult one, as it requires the initial column to be sorted,
which is not possible in this format (users are entering budget information
into different sales reps different products' monthly cells (which are
vertical in terms of the products listed and horizontal in the months going
left to right).

as for index(match()), I have always been confused by these a bit. I just
looked at the help, and it sounds like Index gives quite a wealth of
possibilities. I am not sure how I'd apply it, though. Let me be more clear
about the example, if you have any suggestions...

My users have a template which horizontally shows 12 months. Then the major
grouping of data shown vertically goes as follows:

Product class
sales rep
2004 sales
2004 % of year (how much the shown monthly figure represents of the
whole year for that prod class for that rep)
2005 sales
2005 % of year
2006 BLANK cells for them to fill in
2006 % of year calculated as they fill it in

I intend to lock all the cells other than the 2006 budget numbers which I
want from them. The way that the data is presented is by looking into a
pivot table of all this data. What I ultimately want to do is to take the
actuals I have, take the numbers they provide (which need to stay intact on
the sheet they fill in, as they may need to be updated), and then combine
them all into a pivot table that I can do some analysis and charting on.

Hope that makes more sense. if you have any other creative ways of doing
this, let me know. Thx.

 

[Dave Peterson]

First, =vlookup() doesn't need the first column in the lookup range to be
sorted.

If you want an exact match, you can specify False as the 4th parameter:

=vlookup(a1,sheet2!a:e,3,false)

I don't see a good alternative for what you're doing--but for future info, you
may like Debra's hints/tips.

vlookup is a difficult one, as it requires the initial column to be sorted,
which is not possible in this format (users are entering budget information
into different sales reps different products' monthly cells (which are
vertical in terms of the products listed and horizontal in the months going
left to right).

as for index(match()), I have always been confused by these a bit. I just
looked at the help, and it sounds like Index gives quite a wealth of
possibilities. I am not sure how I'd apply it, though. Let me be more clear
about the example, if you have any suggestions...

My users have a template which horizontally shows 12 months. Then the major
grouping of data shown vertically goes as follows:

Product class
sales rep
2004 sales
2004 % of year (how much the shown monthly figure represents of the
whole year for that prod class for that rep)
2005 sales
2005 % of year
2006 BLANK cells for them to fill in
2006 % of year calculated as they fill it in

I intend to lock all the cells other than the 2006 budget numbers which I
want from them. The way that the data is presented is by looking into a
pivot table of all this data. What I ultimately want to do is to take the
actuals I have, take the numbers they provide (which need to stay intact on
the sheet they fill in, as they may need to be updated), and then combine
them all into a pivot table that I can do some analysis and charting on.

Hope that makes more sense. if you have any other creative ways of doing
this, let me know. Thx.

 

[Guest]

interesting. I thought that at least in old versions when I used to use
vlookup, it required sorting, as it would stop when it passed the point that
your value was passed. In other words, I always thought it looked for either
exact or closest match, but then it stopped once it was in the alpha sort of
what you were looking for (either giving back the next value if not exact and
you didn't do 'false' or not giving anything if it didn't find your value).
Either way, I will try to think through the logic of index and match to see
if they can get me to where I need to be. Thx.

 

[Guest]

GOT IT! Transpose(name range of the vertical array).

Any pitfalls you see? The naming of the arrays will be easy for me, so
assume that in your answer.

Thx for any thoughts.

 

[Dave Peterson]

Just the normal fears when working with a worksheet <bg>. Inserting/deleting
rows and columns kind of thing.

But you know what you're doing with the data...

GOT IT! Transpose(name range of the vertical array).

Any pitfalls you see? The naming of the arrays will be easy for me, so
assume that in your answer.

Thx for any thoughts.
--
Boris

First, =vlookup() doesn't need the first column in the lookup range to be
sorted.

If you want an exact match, you can specify False as the 4th parameter:

=vlookup(a1,sheet2!a:e,3,false)

I don't see a good alternative for what you're doing--but for future info, you
may like Debra's hints/tips.