formula using cell coordinates

[abecker]

I am trying to covert a Lotus 123 spreadsheet to Calc and I can not seem to find a way to convert this 123 formula.
the formula uses cell corrdinates instead of the cell address. The main reason for using this is to get to different sheets without having to know the sheet name.
An example of the 123 formula is:
@@(@COORD(2,5,88,8))

In this example you are referring to the cell in the 2nd sheet, 5 columns over and 88 rows down. The 8 at the end is a 123 notation selecting the type of reference you\ want( absolute, varialble or mixed reference).

you can use the above reference in a formula and it is treated the same as a normal cell address.

Is there anything similar in Calc or a way of accomplishing the same thing.
Any help would be greatly appreciated
thanks

[denmarks]

OOo uses the format sheet1.A1 to refer to a sheet and cell. A sheet name can be absolute or relative and will change names if affected by inserted sheets or sheet renames.

COORD(2,5,88, could be initially set up as sheet2.B88 and will change automatically if sheet names change. $ in front of a sheet name has the same absolute or relative effect as on cells.

You can always use meaningfull sheet names and cell names such as year2006.jantotal.
_________________
Dennis Marks

[Dale]

Try the ADDRESS and INDIRECT functions. Read Help and get back to us if you need more info.
_________________
Dale
To err is human, but to destroy your slippers in the process takes a real son of a bitch: Me!

OOo documentation from the source
http://documentation.openoffice.org
Guides, FAQ, How Tos

[noranthon]

The short answer to your question, I believe, is that there appears to be no way in Calc to refer to a Sheet without using the sheet name. It is possible, however, to leave the names to the default, which is Sheet1, Sheet2, etc.

[RickRandom]

Another function that might be relevant here is OFFSET, which will get the contents of a cell that is offset a number of rows and columns from another cell.

[Villeroy]

[abecker]

Thanks for the responses but I do not think those solutions will work. The project I am working on is a template. Each customer would have a separate file and we update the template every year. So the sheet namesare the date of the update - thats why I need a formula that does not need the sheet name. The next thing is a summary page that compares the last 4 years. That is why a relative reference will not work--because the comparision page only wants the last 4 sheets and those will change every year because we are adding a new sheet each year. That is why the Lotus formula worked well because it worked on cell coordinates a more absolute reference and sheet names did not matter.
The work around I have been trying is to creat a macro to discover the last 4 sheet names and store them as a variable. Then have the macro fill in the formulas; but I been having problems getting a variable to work in a formula. Hopefully someone can help. What I got so far is:

Sub DiscoverSheetName

dim oSheetOne as Object
dim oSheetTwo as Object
dim oSheetThree as Object
dim oSheetFour as Object

oSheetOne=ThisComponent.Sheets().getByIndex(0)
FirstSheet=osheetOne.Name()
oSheetTwo=ThisComponent.Sheets().getByIndex(1)
SecondSheet=osheetTwo.Name()
oSheetThree=ThisComponent.Sheets().getByIndex(2)
ThirdSheet=osheetThree.Name()
oSheetFour=ThisComponent.Sheets().getByIndex(3)
FourthSheet=osheetFour.Name()

Print "1st sheet name is " & FirstSheet
Print "2nd sheet name is " & SecondSheet
Print "3rd sheet name is " & ThirdSheet
Print "4th sheet name is " & FourthSheet

oSheetOne=ThisComponent.Sheets().getByIndex(0)
osheetone.getCellRangeByName("a1").setFormula("=SecondSheet.c4")

Ignore the print commands they are just my way of testing. The last line is the problem.
I have assigned the sheet names to a variable but I can not use it in a formula. Anyone have any idea or I going about in the wrong way.
Any assistance would be greatly appreciated

[Villeroy]

How about this short function which can be used in a cell:
Retunes #VALUE on wrong index.

[Villeroy]

What I would try at the end of period (possibly with a short macro):
named ref thisSheet
=MID(CELL("FILENAME";IV1);FIND("#$";CELL("FILENAME";IV1))+2;256)
returnes name of this sheet:
Select all sheets
Select next free column of all sheets
=thisSheet
Copy, Paste special values
Put everything in a single list
Optional: Make this table a readonly data-source
Do aggregation with pivot-tables by customer, year, month, quarter, you name it.

[noranthon]

[Villeroy]