displaying data in a custom table format, cant seem to autofill???

[totterfield]

Hi, i`m a novice at excel and i don't know what else to do. on the attached file sheet 2 is the raw data that i collect on site, i want to be able to display it in the format on sheet 1. I`ve tried to link the data by using = then the corresponding cell but when i drag it down it misses every 3 (might be something to do with the merged cell.

For example i want

sheet 1 A4 to = sheet2 A2test template.xlsx
sheet 1 A7 to = sheet2 A3
sheet 1 A10 to = sheet2 A4

# and so on. instead when i drag it down i`m getting sheet1 A4 = sheet 2 A2, but the next row (that i`ve dragged down) is sheet 1 A7=sheet 2 A5 - this is missing 2 whole rows of my data.

I have no idea what sort of formula i would use or if there is a simple way to do this.

Any help would be massively appreciated.

Thanks in advance

[Crooza]

In A4 (the merged cell in your sheet , put this formula

=INDIRECT("Sheet2!A"&((ROW(A4)+2)/3))

then copy down (note you'll need to merge the other cells below the table to make this work

[totterfield]

Thanks for the reply but that doesn`t seem to work for me either, when i drag it down it skips 2 data rows in sheet 2, A7 instead of the next one down which would be A5

Sheet1 A4 =INDIRECT("Sheet2!A"&((ROW(A4)+2)/3))
Sheet1 A7 =INDIRECT("Sheet2!A"&((ROW(A7)+2)/3))

[Crooza]

Ummm. That's what you asked for wasn't it? You've got a bunch of merged cells - 3 into 1. As such your merged cells are now named A4, a7, A10 and so on as you progress down the column. You're trying to match cells that are 3 apart with cells in sheet 2 that are one cell apart so when you drag down you get the same sequence.

You said in your original post you wanted A7 to match to A3. Now you want it to match A5!

I'm lost.

[totterfield]

Ummm. That's what you asked for wasn't it? You've got a bunch of merged cells - 3 into 1. As such your merged cells are now named A4, a7, A10 and so on as you progress down the column. You're trying to match cells that are 3 apart with cells in sheet 2 that are one cell apart so when you drag down you get the same sequence.

You said in your original post you wanted A7 to match to A3. Now you want it to match A5!

I'm lost.

Yes. Sorry if ive cpnonfused the issue, but what your describing is what i want. The formula you suggested skipped 3 rows in sheet2 instead of every row. Maybe im not using the correctly, do i need to activate or something? What i pasted in the last post was from a4 where i inserted the formula and a7 ( the next cell down). The formula jumped 3. Feel like im not describing this very well

[FDibbins]

The way you want to adjust your data table is highly unusual, to say the least. Unless there is a very specific reason for changing to this, I would advise maintaining a regular 2D table - excel is designed to work this way, and while what you want is not impossible, it could become messy, PLUS, your data in your new table will be pretty much unusable for any further analysis

[newdoverman]

Enter this formula in Sheet1!A4

Please Login or Register  to view this content.

With the merged cell (A4, A5, A6) selected, hold down the Shift key and hit the down arrow then Ctrl + D to fill.
result
Capture.JPG
If you unmerge the cells and enter the formula in Sheet2!A4 and fill down, this is the result.

	A
4	G1
5
6
7	T1
8
9

[totterfield]

Thanks this is just what i was after. i can use that formula with some modifications for the whole table !!! brilliant thanks

In response to how the data will be arranged, it is for printout survey for people to use (want it to look a bit more interesting that just a plain box full of numbers, the data will never be manipulated again.

Thanks for all the help