CODEWITHSUNDEEP
excelexcel-formula
Deborah
Deborah

Reputation: 1

Offset cells in Excel on different Sheets

I have an exported data and I'm trying to move it arount so it's easier for my boss to read. The exported data are on 3 lines per record so it looks like this:

Name    A    Date    Total
        B
        C

And I need it to look like this:

Name  A  B  C  Date   Total

So I've created a second sheet and am using a formula in cell A4 =Sheet1!B9

refering to the Name and then I have manually done it so it increases by 3 for each record (person). But it's a pain in the butt to do for all 6 columns. I know there has to be a way to do it using OFFSET, but I can't figure it out.

Is there a way to tell Excel to take the cell that is next to a referenced cell? i.e. if cell A4 on Sheet2 is pointed as above to B9, is it possible to use OFFSET or some other function to tell cell A5 to point to one cell down from that cell by pointing at A4, instead of having to link it directly?

Thanks!

Upvotes: 0

Views: 538

Answers (1)

kolcinx
kolcinx

Reputation: 2233

If the data is as shown below, and the desired result as well the solution is beneath the picture.

enter image description here

Below are the OFFSET formulas for the first row in the RESULT area. They can be copied down to produce the result shown in picture.

For Name, A's, Date, Total the formula looks like this:
=OFFSET($A$1;(ROWS($E$1:$E1)-1)*3;0), the only part that changes is the first argument, ex. $A$1-For Name; $B$1-For A's; etc.

For B's the formuka looks like this:
=OFFSET($B$1;(ROWS($E$1:$E1)-1)*3+1;0)-notice the +1

For C's the formula looks like this:
=OFFSET($B$1;(ROWS($E$1:$E1)-1)*3+2;0)-notice the +2

Upvotes: 4

Related Questions