LegendKiller
Lifer
I am pretty good at Excel, but this problem has me stumped...
Every month I get a report that has 5 columns, of which 4 are mostly headers and the last contains data, approximately 290 rows worth. The rows are matched up and stuck into a tracking sheet, with every row lining up. The first 4 header columns are kept consistent and aren't tracked, naturally. The data looks like below. D1 will be the first month, with following months in each column.
HHHH M1-M2-M3-M4
HHHH M1-M2-M3-M4
From this data I need to pull specific numbers, which goes into another tracking sheet which reports on the numbers, calculating further formulas. There is approx 100 "months" of data, with the headers as columns but the actual data in rows. The data is structured like this.
H H H H
M1 M1...
M2 M2...
M3 M3...
M4 M4...
So, the problem is that when I drag down the M4 to be M5, it naturally looks at the report sheet and drags down M1 2nd piece of data, since when you copy cells down, it uses the formula reference below the foruma from above. If you are dragging down a formula referencing cell M32, if you drag it down it will reference M33.
What I want is that if I drag down M32, it will reference N32.
Obviously I could do a transpose of either one of the data tables to match rows to columns, but the amount of data and the way its structured and the graphs referenced to it would equate to a massive pain in the ass.
Does anybody know of a way to drag down but reference to the side???
edit: I know about copy->transpose, but that doesn't get me what I need.
Every month I get a report that has 5 columns, of which 4 are mostly headers and the last contains data, approximately 290 rows worth. The rows are matched up and stuck into a tracking sheet, with every row lining up. The first 4 header columns are kept consistent and aren't tracked, naturally. The data looks like below. D1 will be the first month, with following months in each column.
HHHH M1-M2-M3-M4
HHHH M1-M2-M3-M4
From this data I need to pull specific numbers, which goes into another tracking sheet which reports on the numbers, calculating further formulas. There is approx 100 "months" of data, with the headers as columns but the actual data in rows. The data is structured like this.
H H H H
M1 M1...
M2 M2...
M3 M3...
M4 M4...
So, the problem is that when I drag down the M4 to be M5, it naturally looks at the report sheet and drags down M1 2nd piece of data, since when you copy cells down, it uses the formula reference below the foruma from above. If you are dragging down a formula referencing cell M32, if you drag it down it will reference M33.
What I want is that if I drag down M32, it will reference N32.
Obviously I could do a transpose of either one of the data tables to match rows to columns, but the amount of data and the way its structured and the graphs referenced to it would equate to a massive pain in the ass.
Does anybody know of a way to drag down but reference to the side???
edit: I know about copy->transpose, but that doesn't get me what I need.
