0

How to make formula which works like this:

Taking data form one sheet above and one from the right against the selected one.

So, it should be as shown on the picture:

enter image description here

G3 is equal to "=C3", H3 should be equal to "C+1 3+1" and I3 should be "D+1 4+1".

Is is possible to manage somehow without macros or how to configure macro?

1

Use the INDIRECT and ADDRESS function, together with the ROW and COLUMN function. I don't know how you want to define the first cell (in this case C3), but this should work:

On cell H3,

=INDIRECT(ADRESS(ROW($C$3)+1;COLUMN($C$3)+1))

On cell I3,

=INDIRECT(ADRESS(ROW($C$3)+2;COLUMN($C$3)+2))

And so on. If you put the numbers 1, 2 etc in Row 2, then you can write down the formulas in cell G3 as:

=INDIRECT(ADRESS(ROW($C$3)+G$2;COLUMN($C$3)+G$2))

And when copying that to the other columns, it should work properly on the references, since we will work with relative references. It should look like this:
enter image description here

1

You can use OFFSET for that:
=OFFSET($C$3,COLUMN(G3)-COLUMN($G$3),COLUMN(G3)-COLUMN($G$3))

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.