Issue with Excel Formula?

Question

Hello,

I am building a spreadsheet to house some employee information. I am adding information on one sheet (say A) and need some of that same information to transfer to another sheet (say D). The information from sheet A goes down in a row; while the information needs to go across a row on sheet D. I need it to auto pupulate so that when somethng on A changes, it changes on D as well. When I use the formula =A!D5 and copy, it automatically jumps to =A!F5. How can I get it to increase numerically, not alphabetically or what other method can I use to save from inputting over 1,000 formulas?

Thanks in advance!

Answer 1

If I understand you correctly:

1. You have SheetA and SheetD

2. You have a column of data on SheetA (let's say 10 cells going from D5 to D14)

3. You want to use the same values on SheetD, but in a row (let's say 10 cells going from A1 to J1).

4. You want to set a formula up in the first cell (A1) on SheetD, then copy/paste that formula to the other cells in that row (B1-J1) so that they all reference the transposed cells (D5-D14) on SheetA.



Here's how to do it:



1. Go to SheetD and click on cell A1.

2. Enter this formula into cell A1:

=OFFSET(SheetA!$D$5,

COLUMN()-COLUMN($A$1),0,1,1)



(This formula should all be on the same line, but Yahoo cuts it off.)



3. Click on cell A1 and press CTRL-c to copy.

4. Select the range B1-J1 and press CTRL-v to paste. (EDIT: if you want to do this for 1000 cells, select B1-ALL1 and then paste. Yes, column 1000 is ALL.)



Any time a value in SheetA cells D5-D14 change, the updated value will be reflected in the equivalent (transposed) location on SheetD cells A1-J1.



Make sure if you insert or delete rows or columns on SheetA, that it does not mess up these formulae on SheetD.

Answer 2

kinds paintings vertically, so verify you informed the type to type all rows to maintain the horizontal data intact next, use the greenback sign $ to tell your formula to not replace particular factors once you replica formula, e.g. $a12 will shop "a" yet enable "12" to alter whilst filling/copying a$12 will enable "a" to alter, yet shop "12" whilst filling/copying $a$12 won't enable any replace whilst fill/copying

Answer 3

Insert a $ in front of the D in your formula.