Customer Engagement & Dynamics CRM Forum

Expand all | Collapse all

Excel Template

  • 1.  Excel Template

    SILVER CONTRIBUTOR
    Posted 18 days ago
    Hi experts

    I'd like to achieve to following task:
    User can go to the account view, filter some records, select Excel templates, select a template, then export the data.
    The data is then exported to sheet1.
    In the Excel template, there is another Sheet, lets call it sheet2.
    In this sheet 2, I would like to manipulate some data, for example concatenate 2 rows, lets say Company Name and Company No.

    Question
    How does the sheet 2 have to look concerning the formulas? Since I don't know how many records the user will export, on what rows do I need to repeat the formula? At the end of the day, my row a should always contain A & " " & B from sheet1, regardless how many records the user has exported. Of course I need some more (less simple) formulas, but first I have to understand how it works.
    To illustrate this, I have attached the following picture.

    I have seen some help on that, but all the examples were about pivot.

    Thank you in advance.
    Heinz

    Academy - Online Interactive Learning from Experts


  • 2.  RE: Excel Template

    SILVER CONTRIBUTOR
    Posted 17 days ago
    Hi Heinz, could you save yourself some trouble in excel and do these calculations in Dynamics so you are exporting them already concatenated, or simply having them in a view? Depends on how complex you were intending to get with Excel formulas I suppose but Dynamics can do calculated fields and even reference other calculated fields (of course there are limitations to what you can do, plenty other posts on that here I think)

    ------------------------------
    Robin Marshall
    Dynamics Competency Centre Lead
    Babcock International
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 3.  RE: Excel Template

    SILVER CONTRIBUTOR
    Posted 17 days ago
    Hi Robin

    Thanks for replying in this matter.
    Good advice to think about where to job has to be done.
    Anyhow, I have a special case here and I think it should be quite efficient this way. There are lots of examples, but they are all about pivoting.

    Some more details on what I want to to:
    - There are 2 sheets in the Excel file
    - Sheet1 is the destination for the export
    - Sheet2 is the place where the data should be transformed by formulas.

    My challenge:
    I don't know how many records are exported to Sheet1, so I don't know where (in which rows) to place my formulas. I guess the examples with pivot are a bit different, because they use a named range from sheet1.

    Thanks again
    Heinz

    Academy - Online Interactive Learning from Experts


  • 4.  RE: Excel Template

    SILVER CONTRIBUTOR
    Posted 17 days ago
    Hi Heinz, still sounds more efficient to do the calculation in Dynamics then it will always be done for all items you export. The new field doesn't need to be on the form and you can select which views display it too.

    PowerPivot may be of use as it will apply the formula to the whole range, so if more data is imported than last time it was refreshed it will apply to the new rows too.

    is there a specific reason you need to use the excel template? could a dashboard not do what you need? always keen to avoid excel use myself, unless its for report development and one-off data modelling for example.

    ------------------------------
    Robin Marshall
    Dynamics Competency Centre Lead
    Babcock International
    ------------------------------

    Academy - Online Interactive Learning from Experts


If you've found this thread useful, dive deeper into User Group community content by role