Excel formula in Data Range, row members are not static

[Former Member]

We have a simple scenario.

We have materials in rowkeyrange.

These are not static. Everyday new materials added.

And we have accounts in colkeyrange.

These are constant.

We are using excel formulas in data ranges.

When new materials come, these formulas don't expand.

is there a way to automate this scenario?

Materials	Acoount1	Account2	Account3
Material1	10	2	=20
Material2	20	3	=60
Material3	5	5	=25
Material4	30	10	=????

Assume that material4 has added. There is a formula in the last column.

Account3= Account1xAccount2

Is there a way to do this?

We wont use dimension formulas.

There is no Expansion in the columns.

There is expansion in the rows.

[former_member186498 · Accepted solution]

Hi,

you may just add 2 empty columns right side of the Account3 in the ColKeyRange changing the EVRNG, f.e. if Account3 is in I column from "=EVRNG(G14:H14)" to "=EVRNG(G14:H14; J14:K14)" leaving the J14:K14 cells empty, so even the Account3 formula will be expanded. 

Kind regards

     Roberto

[Former Member · Accepted solution]

Thanks so much for quick response.

But there is a problem: you are excluding Account3 column in colkeyrange, so that it is impossible to send this data to database. I mean; we need "I" column in colkeyrange.

[former_member186498 · Accepted solution]

Hi,

you cannot have a formula in a data region, after the expand you will loose it, because evdre substitute the formula with the retrieved data, so, or you use this column for data entry or with a formula (eventually you can add an EVSND for this column).

Kind regards

     Roberto

[Former Member · Accepted solution]

there is no expansion in the columns. So the formulas won't be overwritten. There is only one problem: how to expand formulas automatically when the new rows come??

[former_member207117 · Accepted solution]

I believe you want to send data to this Account3, but not to receive the same when it is refreshed, although Account1 and Account2 should be refreshed.

You could use EvSND, (but it would not expand) and another option would be to use 2 seperate EvDRE, one to retrieve and another one to send the data.I have used the multiple EvDRE without any problems, across multiple applications too. They could have the same row range, but different column ranges.

[former_member186498 · Accepted solution]

Hi,

if the Account3 it's a product of the other two Accounts,  I think it's not a good idea to calculate this in a input schedule and send the value to the DB, it's better using a dimension formula if you doesn't need to memorized the value, or if you need the Account3 value, to write a simple script logic including it in the default logic and so you can show it, in read only in the report/input schedules.

Kind regards

     Roberto

[Former Member · Accepted solution]

our customer doesn't want it. we must use excel formulas.

[former_member207117 · Accepted solution]

If scale is not of much concern, you should be just fine.

I agree with Roberto's comments, and would encourage you to share the scalability issue with the client, if applicable. Perhaps the client(real life) needs can be better met with a Dimension Formula and  a validation report, where you can show the three accounts from the database and then an excel column calculation to show the Variance is actually zero.

Such calculations happening in the background makes business concerned about the process and a trust needs to be earned by the developers / IT. This relates to a typical Volume x Price = Sales calculation in a Sales Planning situation.

If this is an educational exercise, it exposes you to when multiple EvDRE may be useful to meet business requirements / needs.

[former_member186498 · Accepted solution]

Hi,

then it's better to use EVSND as I and Sunil suggested before.

About the expansion of the formula in MS it works fine, if you're not able to expand even the formula maybe this is the reason http://service.sap.com/sap/support/notes/1491146

But I will try to explain to the customer that this isn't the better way to solve this.

Kind regards

     Roberto