CODEWITHSUNDEEP
excel
RyanMe321
RyanMe321

Reputation: 197

Excel reference cell across sheets custom increment value

I have a data set which needs to be reorganized (see below), I have done the first set of cells by hand, now i would like to have the cell reference increment by 10.

ie. E2 is this:

=Excel.Sheet.12|'data 2015.xlsx'!'!Littoraria!R3C4'

and then I would like E20 to be this:

=Excel.Sheet.12|'data 2015.xlsx'!'!Littoraria!R13C4'

My data set

How I would like it organised

Upvotes: 0

Views: 755

Answers (1)

dev1998
dev1998

Reputation: 892

You can use the OFFSET function to help you out. I'll provide you with a start and hopefully you'll spot the pattern of what I am doing.

First of all go to your original data and get rid of the word "group" in column A. For example, change "group 1" in cell A2 to be a number 1. Do that for all of them. We will need actual numbers for the OFFSET function to work.

I named the source worksheet = "Littoraria", and I created another worksheet named "Target".

In Target cell A2 I put =Littoraria!A2. In cell A3 I put '=A2'. I then copied cell A3 all the way down to A19. A19 was set to '=A18'. If you then select A2:A19 and paste it into A20, it will almost be what you need. Except A20 will have Littoraria!A20, which is wrong. You need it to be pointing to Littoraria!A12. So I then changed A20 to be =OFFSET(Littoraria!A20,(A19*-8),0). A19 has a value of 1. 1 * negative 8 = -8. A20 offset by -8 puts you at A12 which is where you want to get the value of the next group.

If you now copy A20:A37 and paste it into A38, you'll see that A38 has =OFFSET(Littoraria!A38,(A37*-8),0) which points to the 3 in cell A22 of the source sheet.

Once you have your group numbers in place on the Target sheet, you can do similar mapping for the other values.

I can see that the value you have in F2 of the target came from =Littoraria!D4. I put that formula into my Target worksheet. I then copied it to cell F20, which gave me a formula of =Littoraria!D22 which is wrong. So I modified it to be =OFFSET(Littoraria!D22,(A20-1)*-8,0). Now it correctly points to cell D14 on the Littoraria sheet. I copied that to target cell A38 and it became =OFFSET(Littoraria!D40,(A38-1)*-8,0). A38 has a value of 3. 3 minus 1 = 2, 2 * -8 = -16. D40 - 16 puts you at cell D24 which is where that value is in group 3.

Once you get all the data mapping worked out for group 2, you should be able to copy it down for the remaining groups.

Posting those pictures helped a lot. Good luck.

Upvotes: 1

Related Questions