Auto Fill

[kbr]

I have a set of names which are listed in a single row in different columns.
Like Column A has one name and then column B has other name and so on upto
column CZ. All are liste on the same row. Say Row number 3. Now I want to
use these names as a reference in a different sheet of the same excel file.
But, I want the names to be listed vertically instead of them being
horizontally. In short, I want the names to be in different rows but in same
column.

How do I go about it?

 

[KC hotmail com>]

Copy, then Paste Special > Transpose

 

[kbr]

The Transpose doesn't work because I am referencing the names from a
different spreadsheet. When I do Transpose the names show up as a " # REF "
and not the actual name.

I hope you understand what I am trying to say.

 

[KC hotmail com>]

You could copy, paste Values on another sheet, then do the transpose, then
delete the extra unnecessary sheet

 

[kbr]

But, my values keep changing wrt time...

You could copy, paste Values on another sheet, then do the transpose, then
delete the extra unnecessary sheet

 

[KC hotmail com>]

Ahh, then you don't want to copy, you want formulas which reference that
header row perpetually. With normal formulas pointing to Sheet1!A$3,
Sheet1!B$3, etc., auto-fill won't recognize the pattern, even after you've
entered the first 4 or 5.

A possible workaround without using a macro...
Name that list in row 3 (let's call it myList).
On Sheet2, use this formula in A1:
=INDEX(myList,1,ROW())
If you have to start in A3 because A1 and A2 have a title and some headers,
then:
=INDEX(myList,1,ROW()-2)
Now you can use your auto-fill handle.