Here's a Brain Teaser Question for Help

I don't know if this can be done, but I can't see why not however it is Microsoft.

All of my worksheets I use for reporting go from left to right as do most, but one report I do the formulas need to go from right to left, but at the same time the formulas need to be copied going left to right. Do you understand what I'm trying to say???? If I create a forumula and drag it to the left it copies the cells to the left, so say D1-A1, the formulas will go D1-A1, but I need them to go A1-D1. I have tried a million things I hope someone knows what I'm talking about and maybe someone will have an answer. thanks.

Consider you have a range in cells A1:D1.
Now you want A1 in cell D2, and then when you drag to your right, in C2 you want B1; In B2 you want C1; and in A2 you want D1.
Enter the following formula in cell D2 and then simply drag it to your left upto cell A2:
=OFFSET($A$1,0,COLUMN($D$2)-COLUMN())


- Mangesh

that works for just replacing the data but what if I actually want to perform a formula at the same time. Say we have data from A1:D1 and data in A2:D2. Now I want to multiply D2*A1 and now when I drag the formula to the left, I want it to then go C2*B1, and not E2*B1 which excel wants to do. I hope you understand what I'm trying to get at.

Here's what my teased brain came up with:

For your example of data in A1:D1 multiplied by data in A2:D2

In D3 type:

=D2*INDIRECT(CHAR(COLUMN($D1)-COUNT($A1:C1)+64)&"1")

and drag it left to A3

Say we have data from A1:D1 and data in A2:D2. Now I want to multiply D2*A1 and now when I drag the formula to the left, I want it to then go C2*B1, and not E2*B1 which excel wants to do.

Simply use:

=OFFSET($A$1,0,COLUMN($D$1)-COLUMN())*D2

Mangesh