Vertical "autofilling" with horizontal data.

[serdar]

Hi,

I have values in a sheet in cells:

A1,B1,C1,D1 etc.

In another worksheet i wrote

='sheetname'!A1

at F1 and autofill DOWN so i like to have:

F1=A1, F2=B1, F3=C1, F4=D1 etc.

but naturally excel evaluates autofilling as:

F1=A1, F2=A2, F3=A3, F4=A4.

My solution to this; is to use address( ) etc. in a way and it works fine,
but too long. Is there a special way to autofill such that the column part
changes not the row part?

 

[Dave Peterson]

One way.

Insert a new sheet (just temporary).

put
=sheetname!a1
in A1 and drag across as far as you want.

Select those cells.
edit|replace
what: = (equal sign)
with: $$$$$
replace all

with that range still selected
edit|copy

go to its final home (F1 of the real sheet)
edit|paste special|check transpose

Now select that column of cells
edit|replace
what: $$$$$
with: =
replace all

delete that temporary sheet.

 

[serdar]

thanks, thats ok, but not what i want.
i'll keep my current version cos it also skips a specific number of cells
between a defined set of values. I mean for example;
take 10 cells and then skip 3 cells

here is it, it skips 1 cell in every 10 cell (i dont know english function
names sorry, i translate them from turkish but could be wrong):

=DEPEND("'sheetname'!" &
ADDRESS(64;ROW(A2)+((ROUNDDOWN(ROW(A1)-1;10)/10)*1)))