I'm having some trouble autofilling an Excel formula. On my 'Data Entry' tab, there are several columns of data. For every three data entries, there is a merged cell in the column adjacent to the data that performs a 'SUM' function. For example, cells C4, C5 and C6 are summed in a merged cell that covers D4, D5 & D6.
I am trying to transpose the data in the merged cells into another tab for analysis and other functions, including plotting. The formula for this is quite simple - " ='Data Entry'!D4 ". This is in cell A4 of the 'Processing' tab. In Cell A5 of the processing tab, I want the data from the next merged cell on 'Data Entry', so this is cells D7, D8 & D9. Again, the formula is simple - " ='Data Entry'!D7 ". I have entered these two and have the result I need. Problem is, there are several thousand merged cells that need transposing into 'Processing'. When I try to autofill my formula down, it fails to skip the cells in between and starts doing this:
='Data Entry'!D7 (So far, so good)
='Data Entry'!D6 (Returns a zero value, because it is merged with cells D4 and D6 on 'Data Entry')
And so on.... I've filled in several formulas to try and get it to work, but no matter how far I go (I got to D34) it just refuses to work.
I know that I can use the formula " ='Data Entry'!D4:D6 ", and that this will solve the issue, but this will not plot in the way I need it to. I need a 'Scatter with Lines' graph, and when I tried plotting the transposed data using this formula, the lines on the graph were notably absent. As far as I can tell, this is down to the formula, because it works fine when I use the " ='Data Entry'!D4 " style formula.
Please let me know if any other information is required - I've tried to be as descriptive as possible without confusing the issue.
Many thanks in advance