Relative cell references between sheets in same workbook

[cstanford]

Does OO Calc support this? Excel does not. Really should have said absolute or partially absolute cell references between sheets. I need to refer to a row of data on one sheet that is arranged as a column on another (think years as columns in one sheet vs. arranged as rows on another) and when trying to copy the partially absolute cell references OO won't do it. Excel won't either, and this has been documented apparently, but I was wanting to confirm that OO has the same problem.

Thanks in advance...

[gerard24]

Yes Calc (unlike Excel) support relative reference for sheet in the same way of columns & rows.

$sheet1.$A$1 refers to A1 on sheet1.
sheet1.$A$1 refers to A1 on sheet1 if formula is in sheet2. Copying the formula in sheet3 and the formula becomes sheet2.$A$1

[Villeroy]

Having Sheet1, Sheet2 and Sheet3, =Sheet2.$A$1 on Sheet1 refers to A1 on the next sheet. When you copy this reference to Sheet2 it will read Sheet3.$A$1. $Sheet2.$A$1 actually refers to A1 on Sheet2.
When you copy a cell with reference =Sheet2.$A$1 to any sheet of another document, it will also refer to A1 on the next sheet in that document regardless of the sheet name. If there is no next sheet, you get #REF!$A$1
When you copy a cell with absolute reference =$Sheet2.$A$1 to any sheet of another document, it will also refer to the second sheet in that document regardless of the sheet name. If there is no second sheet, you get #REF!$A$1

[cstanford]

Apparently can't copy a partially absolute cell reference like A$1 (that also refers to another sheet). I need to copy a cell reference to another sheet (call it Sheet2) in cells in Sheet1 but keep the row reference fixed in Sheet2 with only the column references changing to Sheet2.

Again, this is a feature that has been documented NOT to work in Excel. I was just hoping it would work in OO but I've tried and it apparently does not.

[keme]

One cell reference cannot point to a row in one sheet and a column in a different sheet. You may have a formula with multiple references.

To keep a reference pointing to the same sheet regardless of which sheet it is copied to, you need to have an absolute sheet reference:
Assuming formula in Sheet2: =Sheet1.A1+A1+$Sheet3.A1

Copy it to the same location in Sheet3, and it should read =Sheet2.A1+A1+$Sheet3.A1

In that formula copy:

• The first A1 has a relative reference to "previous sheet", which is updated upon copy. (Refers to Sheet2)
  The second has an implied relative reference to "this sheet", which is maintained as such and moves with the formula. (Refers to Sheet3)
  The third has an absolute reference to Sheet3, which is also maintained, and does not move (Refers to Sheet3)

Does this address your situation?

A different situation is when you have a range spanning multiple sheets. That is also possible in Calc, but you don't have real 3D functions/operators, so the use is limited. You can do sums and averages across sheets, but dynamic range operations and such (OFFSET, xLOOKUP, MATCH) still need to work on a flat table.

[cstanford]

Won't work or is a harder workaround than just manually addressing each cell once. I'm simply trying to address data organized by year in a column on one sheet with that data transposed to rows on another sheet. I want to address the first cell on sheet2 on sheet1 and then just copy the address down using a partial absolute reference to pick up the values on sheet2 (which will change with different scenarios, sheet1 recaps the scenarios).

[keme]

Got it. Sorry I misunderstood you first time around. This should do a full transpose of a matrix starting in top left cell:
=OFFSET($Sheet1.$A$1;COLUMN()-1;ROW()-1)
Works the same in Excel, I think (different separator characters, but same principle and same functions).

If you only need formulas to transpose one single column to a row, it is simpler, but you need different anchor references and adjustment factors (depending on layout and possibly also context). You may be able to solve it yourself based on the above formula. If you need more help, it is easier if you post your file. If that is not possible, supply the following info:
Name of the sheet containing source data.
Range of cells which constitute the column to be referenced. (Source)
Range of cells where you want the resulting row. (Target)