Copy cell formulas in 2 directions (vertical and horizontal )

[b_bds]

Hi I'm trying to copy a cell in 2 directions but the formula has links that don't follow

here's an example :

A1(value)
A2(Value) C2(= $A$1*A2)


If I drag A2 and C2 down it works they all relate to A1 because of the $ and to the cell beside because of the lack of $.

When A2 and C2 are dragged and filled with multiple data I want to copy all of it to the right (A1, A2 and C2 become H1, H2 and J2 etc) but when I do that I need to fix the formula to every beginning of the selection and redrag everything down....

Is there a way to automate that? It's something that should exist and and tired of searching everywhere.....

Thanks
Bruno

[alansidman]

What should D2 look like after you make the repairs.

[b_bds]

it would look like this :
A1(value)
A2(Value) C2(= $A$1*A2)
A3(Value) C3(= $A$1*A3)
... (column A)

D1(value)
D2(Value) F2(= $D$1*D2)
D3(Value) F3(= $D$1*D3)
... (column B)


I would like to copy column A to become column B etc I have like 80 and so column to do (that's why I want to make it automatic). once that is done i'd also like to copy the big section of rows into multiple rows....

Hope this helps

[Aardigspook]

Instead of =$A$1*A2, try using this:

Please Login or Register  to view this content.

If you then copy A1:C3 to D1:F3, the formula in C2 will automatically give you D1*D2.

Hope that helps.

[b_bds]

Don't seem to be able to apply the offset function...

So I think it'll be easier to give my current formulas and working throught that it'll be easier..


A1= value
A2= 0.9*A1
A3 = value
A4 = value (...) b4 = $A$3*$A$2*A4/$A$15 (...) c4=b4+(1-$A$3)*$A$2/$A$16
...
A15=SUM(a4:a14)
A16= value


I want to be able to drag down from 4 to 14 to create my basic table. Once the basic table done I want to copy the table to the right 40ish times.... Once the 40 copies are done I need to copy that big set of rows down to create another set of row..... I hope this is clear :S

Now I need to retype (adapt) the formulas and redrag from row 4to 14

is it possible?

[FDibbins]

I think it would be helpful if you could upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[Aardigspook]

Try this:
In B4:

Please Login or Register  to view this content.

In C4:

Please Login or Register  to view this content.

This will allow you to copy the A1:C16 range anywhere on the worksheet and it will replicate the formulae, as shown in this pic:
OFFSET to replicate absolute refs in two directions.jpg
Here's the file showing it working. Note that it assumes that all the references in your post above remain relative, unless they have $ included.
OFFSET to replicate absolute refs in two directions.xlsx

Hope that helps - if not, we really need a sample file before we can go any further.

[Aardigspook]

Amendment to above, as I've just re-read that you want to drag B4 and C4 down to B14/C14:
In B4:

Please Login or Register  to view this content.

In C4:

Please Login or Register  to view this content.

Now those formulae will drag down fine, as shown here:
OFFSET to replicate absolute refs in two directions v2.jpg