TM1 Forum

Hi. I have 2 worksheets. sheet1 has an action button that 1. runs a TI to create a new element (then refreshes so a DBRW can get the new element) and 2. goes to Sheet2 passing the new element (from the DBRW cell) as a param to a SUBNM in Sheet2

I have a bunch DBS in Sheet 2 which i want to save against the newly created element passed in to the DBS.

The problem is that sheet2 will run the DBS against the orginal element in the SUBNM first .. then update the SUBNM from the sheet1 param and then run the DBS again agianst the new SUBNM element.

Is there a way to update the SUBNM in sheet2 without doing a refresh (and thus executing the DBS)? So only refresh (and thus run the DBS) once the SUBNM has been updated?

Cheers

LUka

I think what you want to do will work if you break sheet2 into a separate workbook. If the sheet isn't open at the first recalc then you should be ok.

Hi thanks for the reply. I forgot to mention that the DBS in Sheet2 get their values from Sheet1 .. a seperate workbook wont work ( i think?)

sheet1 .. user enters data and hits action button -> TI creates new element -> goto sheet2
sheet2 .. new element loaded in SUBNM .. data from sheet1 saved against new element via DBS
save xls

so when xls is opened again and the user edits data in sheet1 .. hits the action button .. a new "version" elemnet is created and this new version is passed to sheet2 for sheet2 to save data against (with DBS)

the problem is that before sheet2 can use the new element in SUBNM (passed as a param from the action button ) it does a refresh and saves the edited data aginst the orginal (ie old) "version" element first .. then updates the SUBNM with the new element and then refreshes again ... saving the data against the new version element.

Two possible solutions spring to mind. The first is the simplest with least work and change to your current spreadsheets.

Either
Use a Boolean switch to choose whether your DBS cells send or not. This could be a true/false cell value on either sheet1 or sheet2 and the DBS cells woudl then look something like ... =IF($A$1,DBS(.....),"")

Or
Split sheet1 and sheet2 into separate books and use contextual navigation from the action button to pick up cell values from sheet1 and populate cells in sheet2 that the DBS formulas are linked to.