Excel: autofill a column with values from row

Factual Questions
#1

I’m using Excel 2008. I’ve got an extremely basic formula, nothing complicated, just filling in info verbatim from a different sheet. I need to autofill a column on Sheet A with this formula, but the info is coming from a row on Sheet B.

What I need for it to fill in is:
=SheetB!A3
=SheetB!B3
=SheetB!C3
=SheetB!D3
…

but what Autofill thinks I am trying to fill in is:
=SheetB!A3
=SheetB!A4
=SheetB!A5
=SheetB!A6
…

I’m certain there must be a way to accomplish this seemingly simple task but I’m not familiar enough with all of the functions to figure out a formula to make it do this.

#2

Can you transpose the row into a column first or does it have to stay a row?

#3

If it’s an infrequent thing, you can copy and paste special, clicking the transpose box.

If you want to use a function, it’s array transpose.

#4

I’d prefer it remain a row. It’s arranged that way for a reason. I suppose I could add an extra column, transpose into that and then hide it, but the same problem would arise when the values change, which they will.

If it were just a dozen or so cells I would just manually enter it but I’ve got several hundred.

#5

So can you transpose and hide on Sheet A and Link to that on Sheet B?

#6

No. The source values from Sheet B are going to change, which would necessitate copying, transposing and hiding every time a change is made which will ultimately be more work than just manually entering all of the cell addresses.

#7

I’ve never used transpose before, but when I just played with it when I changed the original cell it changed the transposed cell as well.
Obviously it’s a bit ugly having it double linked there, but it does seem like the data will be current.

#8

It’s not working that way for me.

#9

Use a pivot table.

#10

Will you regularly add new rows to the table? If so, only at the bottom or will they be added to the middle?

#11

Not likely. The table is set up exactly how I need it to be, but the data on the table will be changing regularly.

#12

A pivot table can take data from rows and convert to columns, and vice versa. Right-click the table to update the values. Very powerful and easy to use.

#13

The {TRANSPOSE()} array function does exactly what you want.

The one trick you have to know is, when you enter the formula and select the target cells, instead of hitting “Enter” you have to hit “Ctrl-Shift-Enter”.

#14

If for some reason you don’t want to use one of the transpose functions or a pivot table, you could use what I’ve posted below. However, pivot tables are the best thing in the world once you know how to use them. I strongly recommend you use a pivot table.

You could try using the offset command like so:

=offset(x,y,z)

Where x is an absolute reference to the first cell in the range.
y = 0
z = row(a)- b
a = a relative reference to the cell in which you are writing the formula
b = the number of row a.

If you read the manual for offset it will make more sense.