Adjusting rows versus columns for formulas

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

joshua.olson@gmail.com - 10 Apr 2008 08:27 GMT

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.

Reply to this Message

Jim Rech - 10 Apr 2008 13:39 GMT

>>but I'd rather not have to write a visual basic function to translate
>>between the cells.

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.

Signature

Jim

| I'm trying to find any way to adjust the row when I paste the formula
| in the next column or visa versa.
[quoted text clipped - 16 lines]
| 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.

Reply to this Message

pub - 11 Apr 2008 07:00 GMT

joshua.olson@gmail.com wrote in news:7198a016-ca60-4800-bc81-39df38a34906@
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.
[quoted text clipped - 16 lines]
> 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.

Reply to this Message

Rate this thread: