New Ticket Existing and Public Tickets   
Ticket:  373  Created: 10/8/2014 12:00 AM
Status:    Assigned:      
Lean Application / Version:
Priority:       Due Date:  
Name:   CommerceHub    
Email:   -
Phone:    Estimate Hours:  
  Start:    Complete:  

Visible to Requestor Comment User Time
Select In our tests the work column updates ok when changing the dropdown. Could you try using column reference rather than RC[1], so this would be J1 if the drop down is in column J. The formula validation will fail - but when saved this should work fine. If this does not work - please could you send us the task XML file and we will take a look. Lean Software 10/9/2014 12:26:15 AM
Select Still one problem. If I use the dropdown to change the ID selection, it doesn't cause the ID associated with my new dropdown choice to display in the work column. The work column continues to show the value originally computed by the formula. I realize that I could probably unlock that column, and then I would be able to delete the original value and then the formula would probably recompute can cause the new ID to be displayed. But that is a lot to ask of the user. CommerceHub 10/8/2014 10:21:41 PM
Select Wow! That is some serious black magic. I did have to change the example formula that you gave to express J3 as RC[1] instead when entering the formula in Task maintenance. Also, wanted to let you know that when I put the formula in and clicked the 'test formula' it said that I had a formula error. But I decided to save the Task anyway and see what happened. And it worked! Thanks for the tip. CommerceHub 10/8/2014 10:13:29 PM
Select EDT holds the lookup list data in a series of Excel named ranges. The named range has two columns, the first column is the Key value, the second column is the Text value. For example: KeyValue | Text Value 1234 | Company A 2345 | Company B Here is an example formula to get the Key value you are after: =INDEX(LookupList_byKey_col5,MATCH(J3,LookupList_col5,0),1) The MATCH part of the formula gets the row number where the text value matches. The INDEX part of the formula gets the Key value for that row. In the above example the dropdown List is in Column 'J' and the formula is being placed into a preceding work column column 'I'. If your lookup list was in in column K then the formula would be: =INDEX(LookupList_byKey_col6,MATCH(K3,LookupList_col6,0),1) Notice the named range change from col5 to col6. Hope the above to be helpful. Lean Software 10/8/2014 8:05:29 PM
Select I have a column in my spreadsheet setup to validate using a table lookup for an ID / Name pair. Upon initial load, this column does a nice job of showing the Name from the lookup table rather than the ID value that was actually loaded (and will be stored when the Send function is executed). Just to the left of this column I created a work column with a default formula of '=RC[1]' and marked it as 'locked' because I wanted to provide visibility the the ID value, but to require any change to the ID value to be done using the dropdown list on the column that represents that DB table column that holds the ID. Perhaps this isn't most effective way to do this, but I thought it should work. But the default formula of '=RC[1]' evaluates to the Name that was acquired via the lookup rather than the ID value. How can I give visibility to the ID that is loaded / stored, but force any change to be done by choosing the Name for a dropdown list? CommerceHub 10/8/2014 6:35:23 PM


Skip Navigation Links.
Bug report
Expand DatabaseDatabase
Feature request
Help request