Adjusting rows versus columns for formulas

[joshua.olson]

I'm trying to find any way to adjust the row when I paste the formula
in the next column or visa versa.

I would be willing to use indirect references, but transposing my data
would not work well as the data displays much better in the formats
they are in.

Ex. On first worksheet the data is like
D E F G
2 | 25 50 39 27 ....

and the second worksheet I'd like it to be

G
3| Sheet1!D2
4| Sheet!E2
5| ....

I don't really care what method I use to do this, but I'd rather not
have to write a visual basic function to translate between the cells.

 

[Jim Rech]

but I'd rather not have to write a visual basic function to translate
Then you'd have to use an arduous manual process-

Referring to your example-
-Create formulas that go horizontally across a row (i.e. reference D2 and
copy to the right) in a temporary holding location.
-Do a Find/Replace of = with say % on the formulas.
-Copy these "formulas" followed by a Paste Special, Transpose to your
intended final location.
-Do a Find/Replace of % by = on the transposed formulas.

--
Jim
| I'm trying to find any way to adjust the row when I paste the formula
| in the next column or visa versa.
|
| I would be willing to use indirect references, but transposing my data
| would not work well as the data displays much better in the formats
| they are in.
|
| Ex. On first worksheet the data is like
| D E F G
| 2 | 25 50 39 27 ....
|
| and the second worksheet I'd like it to be
|
| G
| 3| Sheet1!D2
| 4| Sheet!E2
| 5| ....
|
| I don't really care what method I use to do this, but I'd rather not
| have to write a visual basic function to translate between the cells.

 

[pub]

(e-mail address removed) wrote in 1g2000prg.googlegroups.com:

I'm trying to find any way to adjust the row when I paste the formula
in the next column or visa versa.

I would be willing to use indirect references, but transposing my data
would not work well as the data displays much better in the formats
they are in.

Ex. On first worksheet the data is like
D E F G
2 | 25 50 39 27 ....

and the second worksheet I'd like it to be

G
3| Sheet1!D2
4| Sheet!E2
5| ....

I don't really care what method I use to do this, but I'd rather not
have to write a visual basic function to translate between the cells.

so in sheet 2 cell G3 you want to just copy down?
copy&paste this formula in G3

=INDIRECT("sheet1!"&ADDRESS(2,ROW()+1))

then copy down

basically you are using the indirect() to indirectly direct your cell (G3)
to pull the cell you want (sheet1 C2)

you are concatenating "sheet1" with the address()
the address() has 2...which is your fixed row.
and row()+1) since you are starting on G3, it takes the row number and adds
1 to get 4 (D is the 4th letter)

so thats how it wokrs. you can adjust for all your future transpositions.

good luck.