Is there a way to move the cell referenced down when I drag to the right or vice versa? Like when I drag the cell with the formula "=C6" to the right, I want to increase the number count into "=C7" instead of "=D6".
Have you tryed manually write 3-4 cells select all them and drag right? not sure it work but worth the try
yes, I have. my hand hurts because of the uncomfortable shift location, so I want to write less (and invest in better keyboard, but that's not achievable right now). there should be a smarter solution than manually doing this. currently learning about offset, which may or may not applicable to my formula (so far failed though, the logic of offset and transposing the cell eludes me).
Make the drag in a cllumn and you will get the c7->c8 that you want, select cell copy or cut and paste speciall with transpose option to makeit a row
for example: =sumif(C$2:C$10,1,X$2:X$10) when I drag it down, I need the C to become D. and when I drag it right, X change to Y. so while I can transpose the column using paste -> transpose, it is quite useless for me. It is true that I can make a cross tabulation using pivot table. Though in this data, I choose not to as the data is too messy.
You have 2 choice See if # of rows or colunms is smaller and manually prepare the first term of all and then drag Make intermedary table to make the data align with the drag