Splashing inside a Dynarange using like

    Splashing inside a Dynarange using like

    Hi everyone,

    in a dynarange I have the

    A - Customer Name
    B - Revenue last period
    C - Budget value next period

    In the cell in the colloum C inside the dynarange I have using datavalidation created a Dropdown list with the folloing elements,

    Insert 10% of value in cell in coloum A
    Insert 20% of value in cell in Coloum A
    etc.

    For this I have added the following coloums to the dynarange, from which the Dropdown list is created

    D - (0.1*value in cell in coloum A) & "LIKE Revenue;Actual"
    E - (0.2*value in cell in coloum A) & "LIKE Revenue;Actual"
    etc.

    This works fine for the first row in the dynarange where I am able to splash the wanted value, e.g. 10% or 20% of Revenue in last period

    BUT

    For the next rows, even though the values in coloum D and E are correct, I can only splash the values from the first row.

    I have tried in the datavalidated to enter the formula

    =D10:E10
    =$D10:$E10

    and everyother possible combination, but I am through out the dynarange only possible to splah the values from the first row!

    Anyone has a good idea?

    Best regards

    Sven

    Unfortunately you can't define a LIKE splash parameter in a SETDATA-function. This would help with your issue.
    But you can meet your requirement with the ugly solution to load (reduced) values from one year to another with ETL, so you have the proportion basis for a #-splash. You could make them small enough to be shown as zeros, if you want.

    I didn't know that you can give "[value] LIKE [element]" as the input to a setdata. Thank you for that info! ^^
    Well, it solves your issue, because then you can just use setdata formulas to splash the values and only write factors in a cell via datavalidation.

    Post was edited 1 time, last by “Seaben” ().