Re: [libreoffice-users] Generic formulas or inverse scenarii

To all of you who answered, thank you. Unfortunately, i think i misexplained 
my problem.

I am a quite good user of spreadsheets, and i use cross-sheets formulas daily, 
with complex schemes. My problem is rather more complicated: scenario cells 
outside of the scenario's scope are blanks.

Try that:
- In a blank sheet (Sheet1), fill the 2 first cells (A1 and B1) with numbers, 
and the third one (C1) with its sum:
  =A1+B1
- Select only the third one (you can select a single cell with CTRL) and 
create a scenario out of it, let's name it "S1", and uncheck "Copy the whole 
sheet" and "prevent changes".
- Now, in another sheet (Sheet2), reference the first sheet's third cell (the 
sum):
  =Sheet1.C1
  it works, you see the sum on Sheet2.
- But instead, reference the scenario's same cell:
  =S1.C1
  you get a 0!
- Make a new scenario on Sheet1, but now selecting the 3 cells (A1:C1), and 
name it S2.
- On Sheet2, replace S1 by S2 in the formula: it works! You see the sum.

What happens is that the scenario doesn't "see" cells that are not in its 
scope. So, although S1 "sees" the formula in C1, they use A1 and B1, which are 
empty for this scenario, and you get a 0. For S2, A1 and B1 are in the scope, 
the sum works.

But in my case, it is not possible to select the whole sheet as a scenario, as 
90% of the sheet is not the part of the scenario and changes only when the 
statistical scheme is adapted. It would be a lot easier to just make as many 
sheets as i have scenarios. But it brakes the purpose of reusability.

To conclude, I see two real solutions to my problem:
- A "pattern" sheet: you write a pattern sheet, in which some cells are 
"reserved for values". All the rest is the mechanism you want to reuse. Then 
you instance this pattern as many times as you want, leading to one new sheet 
by instance. In each instance, you can freely modify the "value areas", the 
rest of the sheet use them and you get your results for this parcticular case.
- Scenarios could have an option "silent full copy": for each scenario, 
internally the whole sheet would exist, using scenario values. So refering to 
a cell that is outside the scenario scope, but by mentioning the scenario's 
name would use the original sheet's value instead, and compute formulas if 
there are. That's almosyt like using the option "Copy the whole sheet" of 
scenario creation, except that this option makes new sheets that become 
independant of the original one, and modifying a cell in the original sheet 
does not affect the copies.

I hope i made it clear enough, sorry if it is still strange :)

Best regards,


Le 15/03/2012 03:05, Jay Lozier a écrit :
> On 03/14/2012 06:49 PM, Ninj wrote:
> > In fact, i thought of possible solution after i submitted this question. Is
> > there a way to link cells between sheets in a way that they are "virtually
> > the same"? Changing B4 in a sheet would change also B4 in other sheets where
> > B4 is linked.
> If cell B4 is on Sheet1 and you wish to automatically update cell E7 on
> Sheet3, enter in E7 on Sheet3 =Sheet1.B4. The syntax is =SheetName.Cell
> > Because my problem is not more complicated than that finally: "i have three
> > sheets almost identical except for the 4 first cells, how do i avoid
> > maintening the three ones when i need to change a cell that is the same in
> > all three sheets?"
> >
> > Then i thought of some kind of link. But i don't know...
> >
> > Thank you!
> >
> > Ninj
> >
> > Le 14/03/2012 22:55, Ninj a écrit :
> > > Hi,
> > >
> > > I wonder if Calc can help in my case:
> > >
> > > I'm writing a financial forecast for my company. We sell 3 kinds of products.
> > > But they all use the same scheme when it comes to computing fees and incomes.
> > > So i made one (big) table with all the computation process and the comments
> > > (it has to be shown to financers). The product variables are isolated at the
> > > top of the sheet.
> > >
> > > Then i used scenarii on my variables (there are only 4 variables to define a
> > > product). It's fine: i can switch scenario and i see all the matching
> > > forecasted results on the computation table.
> > >
> > > But now, i want a new spreadsheet summarizing our activity: we sell the 3 kind
> > > of products, so i make a table with three lines, one by product. Rows are for
> > > years (5 in my forecast). And of course, i would like this table to be
> > > dynamic, and change with whatever variable i change in scenarii or formula in
> > > the forecast table. The problem is scenarii are not the answer, because they
> > > are by definition volatile: when you switch scenarii, all the data,
> > > everywhere, gets adapted. Even naming a scenario and using its name when
> > > refering to a cell doesn't work, because cells out of the scenario's frame are
> > > considered empty.
> > >
> > > So, is there a way to mix the scenario behaviour with dynamic tables mixing
> > > data from all scenarii?
> > >
> > > I thought of generic formulas: i could make only one sheet with all
> > > computation, then 3 other sheets with only the variable data of each of the 3
> > > products, and use the first same sheet formula in the 3 next ones to get my
> > > results (in place). Then a 5th sheet could easily summarize all that. The
> > > first sheet would not have any result, since without the data of the 3 other
> > > sheets, it's only formulas.
> > >
> > > I hope i was clear. Not easy to explain that, but i'm sure you see my problem,
> > > and maybe someone has another, magical, solution.
> > >
> > > Of course i could copy 3 times the whole sheet (data + computation), but that
> > > means much more maintenance, since i'm changing often details in the formulas
> > > (i should do changes three times always). And what about same case but with
> > > 10+ products?
> > >
> > > Thank you for reaching this line of text :)
> > >
> > > Best regards,
> > >
> > > Ninj

--
For unsubscribe instructions e-mail to: users+help@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted