Mass change to selective records with drop-down box

[LoganNSE]

Hi,

There are a few columns in Active form designed as a drop-down box with 5 values and it takes 3 second to change one line of value in drop-down box i.e. to change value from 'A1' to 'B1' for 10 selective records, 'B1 to C1' for 35 consequtive records, 'C1 to D1' for 20 records etc. It took almost 15 minutes to update all 237 records.

TM1: 9.5.2
Excel: 2007

We were unable to select more than once cell with drop-down to change values at once, copy/paste doesn't work etc.

Is there a better and fast way to perform these changes in drop-down box?

Thanks & Regards,
Logan

[rmackenzie]

Maybe you can post a screenshot to go along with your explanation of the problem as I, for one, am unclear about the make-up of your report - so it's hard to try and attempt to answer the question...

[LoganNSE]

Hi,

Please find attached the snapshot of what we needed - 2nd column (RESALE) is a drop-down column with static values populated and users want to change rows-1,2,3 and 7 from 'RESALE' to 'Cost Method' (we have 200+ records and it is painfull to change cells one-by-one). We have to go through each and every cell, click the drop-down and select value - is there any way we can choose all the cells that needs to be changed and apply new value?

Is there any other logic with which we can accomplish? Issue is we were unable to select more than one cell (using CTRL key) at a time in the active form - we were able to select more than one cell by dragging but then the drop-down change applies only for the first cell selected.

Let me know if you need more information and I am open to any kind of suggestions.

Thanks & Regards,
Logan

[tomok]

Turn off auto-recalc, that's what is taking so long more than likely. You don't have to calculate the form for the data to be sent to the cube. It's going to go as soon as you change the drop-down value.

[rmackenzie]

Please find attached the snapshot of what we needed

Thanks! Much easier to understand now

We have to go through each and every cell, click the drop-down and select value - is there any way we can choose all the cells that needs to be changed and apply new value? Is there any other logic with which we can accomplish? Issue is we were unable to select more than one cell (using CTRL key) at a time in the active form - we were able to select more than one cell by dragging but then the drop-down change applies only for the first cell selected.

I don't think there's a native Excel short-cut or key combo, or similar on TM1 Web, that's going to help you here. I am genuinely interested in being wrong about that!

I am open to any kind of suggestions

Technically, if all those elements were related somehow by an attribute, perhaps, or by a consolidation, then there would be some way to automate the entry. However, this is just taking your current problem and re-expressing it i.e. how to group up the elements and I suppose this may not be possible prior to the data entry. Although some TM1 'purists' wouldn't like it, you could use a VBA user-form to allow for just checking and unchecking tickboxes against element names and then selecting the category (Resale, Cost Method) etc from a single dropdown and applying the setting in one go. Also, staying in Excel, there are a variety of Excel add-in tools that allow you to do bulk copy and paste over groups of DBRWs that may help you here. I don't know if the free tool available on this forum can do that? If you are in TM1 Web, then you don't have any VBA options and I think you are back to the problem of 'grouping' outside the active form but you could use some combination of action buttons/ TI to achieve the bulk write.