CODEWITHSUNDEEP
vbaexcel
Nicolaesse
Nicolaesse

Reputation: 2714

Massive offset to entire Excel sheet

I have one MS Excel file with 2 sheets. The first Sheet contains user input and the second one contains formula.

Sheet1

+---+---+---+---+---+---+
|   | A | B | C | D | E |
+---+---+---+---+---+---+
| 1 | 1 |   | 0 | 9 |   |
| 2 | 9 | 7 |   | 3 | 2 |
| 3 | 8 |   |   | 4 |   |
| 4 | 4 |   |   | 8 |   |
+---+---+---+---+---+---+

Sheet2

+---+--------------------------------+
|   |               A                |
+---+--------------------------------+
| 1 | =SUM(Sheet1!A1:A2)             |
| 2 | =PRODUCT(Sheet1!A4*Sheet11!B2) |
| 3 | =MEAN(Sheet1!A1:A3)            |
+---+--------------------------------+

I would like that every formula in the Sheet2 takes an offset of three cell to the right. So, the example, would became:

Sheet2 (after offset)

+---+--------------------------------+
|   |               A                |
+---+--------------------------------+
| 1 | =SUM(Sheet1!D1:D2)             |
| 2 | =PRODUCT(Sheet1!D4*Sheet11!E2) |
| 3 | =MEAN(Sheet1!D1:D3)            |
+---+--------------------------------+

How could I do that? I know that I could use the OFFSET formula but it would be a very ugly solution applied to an entire sheet (with 36000 rows and more than 200 columns).

I don't have any code to post because I don't know how to start solving the problem.

Upvotes: 3

Views: 127

Answers (1)

Sebastian B
Sebastian B

Reputation: 451

If you want to do it just once, a simple solution would be to insert 3 columns on Sheet1 before column A. The formulas on Sheet2 will be offset as you requested. After that, simply cut everything on Sheet1 starting at D1 and Paste it into A1.

Upvotes: 4

Related Questions