|
| The AccuTerm forum has moved. Go to community.rocketsoftware.com to register for the new Rocket forum. |
|
Post Reply 
|
| Author | ||
kitkatinfl 
Newbie 
Joined: May 11 2022 Status: Offline Points: 3 |
 Post Options
 Thanks(0)
 Quote Reply
 Topic: excel sheet calculationPosted: May 11 2022 at 11:11am |
|
|
Hi, I am brand new to Accuterm but a veteran PICK user. I have not done much with excel spreadsheets at all, and certainly haven't created them through PICK. Now I need to! The good news, Im late to the game so you all know this stuff and can give me tips or point me in the right direction - lucky me!! I have a spreadsheet created through accuterms FTD. I want to send a formula? Equation? not sure of the correct terminology yet but - I want to fill a column with a calculation. I am trying to fill it with this: " =INDIRECT("G" & ROW())/INDIRECT("J" & ROW())" Putting this manually into the sheet works. I need to automate this. So we added the code into the data to be sent, and yes! The equation excutes and the cell is filled in with the value BUT the function itself is not left on the cell. The problem here is that if a user alters either of the two fields used in the function, we want the calced cell to recalc, and it wont without the function being preserved in the cell. Any help would be greatly appreciated!
|
||
 |
||
|
PSchellenbach
Admin Group 
Moderator Joined: December 15 2003 Location: United States Status: Offline Points: 2150 |
Post Options
Thanks(0)
Quote Reply
Posted: May 11 2022 at 2:21pm |
|
|
Hi Kitkatinfl - I was able to use FTD to create an Excel worksheet with a formula. Here's what I did, so you can try it. Using the PROD.SAMPLE sample data file in the ACCUTERM account, I added a new dictionary item named FORMULA with an Excel formula as a constant:
Here's the FTD command:
The Excel worksheet accepts the formula in column D, and changing the value in column C causes column D to recalculate. |
||
|
||
|
kitkatinfl
Newbie
Joined: May 11 2022 Status: Offline Points: 3 |
Post Options
Thanks(0)
Quote Reply
Posted: May 11 2022 at 4:53pm |
|
|
Hi Pete, pleasure to meet you! You've written some pretty slick stuff! From what I am reading and trying, there are a number of ways to accomplish what I am looking for. Your above example with the formula in the dict is one way. I haven't actually tried it but I trust you :) I see here at Colwell that often they send an equation within the data field... and that also seems to work. And still other programs send 'objExcel' commands... If I have this correct that is VBA. Can we, do we, use and intermingle all these? All there rules when to use one vs another? Any documentation on PICK into Excel? Hopefully you didn't just laugh, but I get it if you did. As for this particular need, I need different cells to have different formulas so, I think putting the formula in the data element is one way. The header would alter all the data in that column I assume so that would not work here. No, I did not say that up front and apologize for that. It does seem here that if the first data element listed doesn't have a formula (when using formulas within the data) then later formulas ( say row 10) will be taken as literals and not executed. That was what we've found to be the issue in my test anyway. Any insight, documentation, classes, reference, wink of an eye... whatever you can do to help me understand the differences would be great. Thank you for getting back to me.
|
||
|
||
|
Post Reply
|
|
Tweet
|
| Forum Jump | Forum Permissions You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum |
excel sheet calculation
Topic Options