Using fill with increasing columns.

[williamlief]

Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel In one sheet (sheet 2) of my document I have a list that spans multiple columns. In the other sheet (sheet 1) the list is in multiple rows in the same column.
I want:
a1 of sheet 1 to equal a1 of sheet 2
a2 of sheet 1 to equal b1 of sheet 2
a3 of sheet 1 to equal c1 of sheet 2
etc.
I can do this easily enough manually, but I cannot figure out how to do this using the fill function. The current function I am using is:
a1='sheet 2'!B$1
The dollar sign locked me into row 1, but when I try to fill down it just repeats the same function, it doesn't change it to to C$1, D$1 etc.
Is there an anti-$ that will force the column change or is there a better way to do this?
Thanks.

 

[John McGhie]

Remove the $ signs entirely, to enable Excel to vary both row and column.

Then drag across.

The drag down.

Excel will update the cell references appropriately to put the correct
formula in each cell.

Cheers

Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel
In one sheet (sheet 2) of my document I have a list that spans multiple
columns. In the other sheet (sheet 1) the list is in multiple rows in the same
column.
I want:
a1 of sheet 1 to equal a1 of sheet 2
a2 of sheet 1 to equal b1 of sheet 2
a3 of sheet 1 to equal c1 of sheet 2
etc.
I can do this easily enough manually, but I cannot figure out how to do this
using the fill function. The current function I am using is:
a1='sheet 2'!B$1
The dollar sign locked me into row 1, but when I try to fill down it just
repeats the same function, it doesn't change it to to C$1, D$1 etc.
Is there an anti-$ that will force the column change or is there a better way
to do this?
Thanks.

--

The email below is my business email -- Please do not email me about forum
matters unless I ask you to; or unless you intend to pay!

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410 | mailto:[email protected]

 

[CyberTaz]

Here's my reply to a similar post last week. The difference is that the user
had vertically arranged data he wanted to transpose whereas it sounds like
your source data is horizontally arranged, but you'll probably be able to
make the interpolation using this method as a guide;

************************************************************
No offense, but I honestly can't conceive of any reason why anyone could
possibly need to do this. If you fully describe what it is that you're
attempting to accomplish perhaps there is an alternative approach.

If you really must do this, here are 2 approaches;

If you just want the content of N1:N8 copied to C8:K8, select & copy N1:N8,
right-click C8, select Paste Special, tick the Transpose checkbox, then OK.

If you need formula references in C8:K8 it's a bit more involved:

1- In O1 enter =N1, use the fill handle to O8, leave those cells selected,
2- Key Control+U, then Command+T, then return,
3- Repeat Step #2 for each of the cells in column O, then copy those cells,
(Steps #2 & 3 convert the formula references to Absolute)
4- Right-Click C8 & use Paste Special> Transpose as above,
5- Select & clear cells O1:O8.

HTH |:>)
Bob Jones
[MVP] Office:Mac

What if I want to transpose cell references?

eg if a column contains values in N1, N2, N3, N4 ....

and I also want those values to appear in a row as C8, D8, G8, H8, H9

I would select cell C8 and type "=N1"

Ideally I could just select/drag across the row and the references
would follow automatically, unfortunately that's not the case - it
wants to work in the same direction as the dragged row, not the
sourced column.

Any ideas?


Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel
In one sheet (sheet 2) of my document I have a list that spans multiple
columns. In the other sheet (sheet 1) the list is in multiple rows in the same
column.
I want:
a1 of sheet 1 to equal a1 of sheet 2
a2 of sheet 1 to equal b1 of sheet 2
a3 of sheet 1 to equal c1 of sheet 2
etc.
I can do this easily enough manually, but I cannot figure out how to do this
using the fill function. The current function I am using is:
a1='sheet 2'!B$1
The dollar sign locked me into row 1, but when I try to fill down it just
repeats the same function, it doesn't change it to to C$1, D$1 etc.
Is there an anti-$ that will force the column change or is there a better way
to do this?
Thanks.

************************************************************

HTH |:>)
Bob Jones
[MVP] Office:Mac

 

[williamlief]

The second method you suggest works, but it is unfortunately even more labor intensive than what I was doing before. (filling down and then correcting the column letter).
Thanks for your help.

I take it there is no anti-$?

 

[CyberTaz]

I don't have any idea what you mean by "anti-$". The $ is used to designate
absolute reference, so the "anti" is to not include it (relative reference)
which is the default. But that isn't the problem.

You're trying to *transpose* from horizontal to vertical orientation of the
data & create links while doing so. For that there is no "shortcut" or
convenience tool [AFAIK] because it's something most users would never do.

Regards |:>)
Bob Jones
[MVP] Office:Mac

 

[Bob Greenblatt]

I don't have any idea what you mean by "anti-$". The $ is used to designate
absolute reference, so the "anti" is to not include it (relative reference)
which is the default. But that isn't the problem.

You're trying to *transpose* from horizontal to vertical orientation of the
data& create links while doing so. For that there is no "shortcut" or
convenience tool [AFAIK] because it's something most users would never do.

Regards |:>)
Bob Jones
[MVP] Office:Mac

The second method you suggest works, but it is unfortunately even more labor
intensive than what I was doing before. (filling down and then correcting the
column letter).
Thanks for your help.

I take it there is no anti-$?

Teh only other way to do this but it may be more labor intensive is to
switch the row and column designation from A1 to R1C1, then you can
enter your formula, fill it down and/or across, then use find replace.
first replace all the equal signs ro something like ##. Then copy and
paste transpose the formula strings. Then replace the ## with equal
signs. But it is probably easier for you to do it as you were doing
manually.