Problem to Excel dropdown box validation

[Midwest]

Hi,
Please take a look at this screenshot i have

There is a dropdown box for currency on the form. If user type in wrong currency or currency with extra space (ex: "USD "), then it prompt user that invalid currency entered, then it change back to default USD. The issue is when user copy the Currency from other Excel sheet, then paste to it, it won't validate and keep the wrong (mispelled or with extra space) currency as I show the UDD and XYZ there.

How do we validate it when they copy/paste? any help is really appreciated.

Thanks all.
M

[mastertito4]

So a few clarifications. When you copy and paste the other currency in, are you copying and pasting one cell at a time or multiple cells at a time? Also, after copying and pasting the incorrect currency, when you click on that cell again does it have a DBRW function still in it or is it simply DDU, XYZ, etc.?

[Midwest]

Answer:
Users copy/paste multiple cells at a same time.
After copy/paste, the value simply DDU, XYZ. the DBRW is no longer there.

[mastertito4]

Users copy/paste multiple cells at a same time.
After copy/paste, the value simply DDU, XYZ. the DBRW is no longer there.

Would it be possible to have users just copy and past one cell at a time or not copy and paste at all? When you copy and paste multiple cells in an active form or slice, it will erase the TM1 Excel functions in the selected cells and replace them with just plain text or numeric values, and to the best of my knowledge there is no way around that (although my knowledge is not very great when dealing with active forms and slicing). So, if having users not copy and paste multiple cells at the same time is not an option, I have no other good solution for you at this time, but someone else might. A while back, we had a request to take excel slicing offline, and to accomplish this we wrote some VBA for excel which essentially copied and pasted an entire sheet into another sheet when the user wanted to go back online. So I know it is likely doable, it may however be more work than benefit. A starting point might be to look into what Excel Functions you are using in your sheet, whether its DBRW, DBR, etc. and how those are each affected by copy and pasting techniques. Sorry I don't have a better answer.

[Midwest]

Thanks so much for your help mastertito4.
After googling testing and pulling hair, I can't find the solution either.

I tried to use Format Cell-> lock cell
and tried Sheet protection, but still not work.

My solution to the users is having a dropdown box of Currency on their own form, the currency must match with what we have in TM1.....Also suggest them manually enter it.

Thanks again and have a great day!
M

[tomok]

The behavior you are experiencing is a known fact in Excel. Data validation is a property of a cell. When you copy a cell from one spot to another, the data validaton propery goes with it, essentially replacing the setting of the target cell. There is nothing you can can do in Excel to stop this behavior, at least that I know of. However, there are still ways to validate, or at least indicate to users when they have entered invalid data so they can correct. In a column out to the right of the input cells place this formula:

=IF(DIMIX(Dimension,E$1)=0,"Bad","Good") Where E is input column, 1 is the row, and Dimension represents the server name concatenated with the dimension name with a ":" in between. Set the conditional formatting to make the background green if the cell value is ""Good" and red if the cell value is "Bad". If the user sees red then they know there is an error. Obviously, you don't have to use the words "Bad" or "Good" or use green and red. You can be creative but the concept is the same.

[PlanningDev]

Not helpful for excel but I believe one of the hot fixes for 9.5.1 had a webconfig setting to force validation on pasted strings in picklists.

I know your pain though as picklists have a ways to go before they can be trusted in excel for validations.

[Midwest]

Thanks Tomok and Planningdev.....
I will try out you suggestion little bit later. (=IF(DIMIX(Dimension,E$1)=0,"Bad","Good") )

Thanks,
M