Is it possible to get the reference to a cell in another sheet as text, Then change something and use it as a reference again.
So for instance I have two Sheets: Sheet1 and Sheet2
in Sheet 2 in cell A1, I have a reference to cell B2 of sheet 1, like:
=+$Sheet1.B2
Now I would like to convert this to a text string, change the reference and use it again as a reference in stead of a text string.
Is this possible, and which functions do I need.
Thx,
PPee
Mmmm, OK back to the root problem:
In sheet1 I have Meter values per day for energy Usage (Peak/OffPeak/Gas/Solar Production/SolarPeakReturned/SolarOffPeakReturned)
In sheet2 I want to make a reference to the values of previous day, previous week, previous month and previous year.
In my Home Assistant automations I generate this data automatically, so I only need this data when I want to push these values after some kind of mishap. This happens frequently now because I’m working on this part of the Home Assistant setup.
To make the “Previous…” overview in sheet2, I would like to use the OFFSET function of Calc, but offset only seems to work offsetting on the active sheet (Sheet2), and I would like to offset on sheet 1 where the day based core data is. So I thought I could make the reference to sheet 1 in the previous_day row on sheet 2 and calculate the references for previous_week, previous_month and year for all the energy types by subtracting a nr of rows. So if the week shifts -7, if the month shifts -28/30/31 if the year shifts -365.
Pfff, this is the background of my xy problem. I did not want to make it too complicated…
PPee
Hallo
OFFSET works for any other sheet if you set a full Cellreference as first Argument like:
=OFFSET($Sheet1.$A$1; …)
But IMHO in the first place you should insert a Column with the related Date_value to each Dataset!!!
I tried it again, but LO does the offset in the active Sheet2 and not in the referenced Sheet1 as I would like it to do.
I don’t understand your final remark. what is IMHO and what do you mean with the related Date_value for each dataset
Cannot reproduce, pleace attach a Example.ods where we could prove what happens.
Test in Safe mode (menu Help).
Example.ods (12.6 KB)
OK in this example in Proefsheet2 B13 I have the reference to B14 in Sheet1 <=+$ProefSheet1.B14> resulting in 88. That is correct.
So now in B12 of Sheet2 I would like to have a formula which is in fact <=+$ProefSheet1.B12> shifting two rows up in Sheet1.
When I use OFFSET (like I did in the example in Sheet2-B12), it shifts two rows up in the active Sheet2 resulting in 0. Instead I’m looking for a way to offfset two rows in Sheet1, which would result in 66.
Improve your Expectations about OFFSET (the first Argument is the ReferenceAddress ) and use:
=OFFSET($ProefSheet1.$B$14;-2;0)
Yes I know, but that is what I would like to aoid, because then if date shifts, I have to change all references
Maybe it is not possible, but I want to manipulate
$ProefSheet1.$B$14
as text string, for instance change B14 in B12, and then use it as a reference
This would be simple enough using INDIRECT(), SUBSTITUTE() and REGEX(). But you’ve complicated things by using a plus sign between the equals sign and the sheet name. Why do you need it? You write so persistently =+$List... (FORMULA() and VALUE() also need)
It can be
=INDIRECT(SUBSTITUTE(REGEX(FORMULA(B13);"\d*$";"")&(VALUE(REGEX(FORMULA(B13);"\d*$")-7));"=+";""))
Here -7 is “a week ago”
mmm, yeah I don’t need the +. It is just the way I make the reference, by pressing the + sign and then going to the right field with the mouse pointer…
Thx for the relevant formulas. I’m going to check them out
how long will this work?? 
Until subtraction from the row number results in a number less than 1. For example, 30 or 365 rows cannot be subtracted from B14
Perhaps this could be simplified:
=OFFSET(INDIRECT(SUBSTITUTE(FORMULA(B13);"=+";"";1));-7;0)
But with data under my responsibility, I would avoid something like this if possible.
Does this mean that you want to make the sheet reference depend on a date?
Why?
See also tdf#157062. Would this help?
Well, I still don’t understand for what reasons the HomeAssistantAutomationTool must split the data depending on dates.
Are there more than 1E6 Rows of data?
On the other hand I want to get this out of my head. I therefore prepared an example demonstrating a way how I would probably approach the task of “offset by sheets” if this was actually at the core of the problem.
To be able to designate a sheet by its name (as long as simple usage of the index for the purpose strangely is not supported by standard functions) the attached demo is using one of the UDF helper functions taken from my toolbox.
disask97995CalculatedOffsetRanges.ods (35.0 KB)