Dynamically Update Another sheet in Excel

[Kallis]

Hello Friends

In the table given below, Cost Centre, Name and Value are in Sheet 1.

Document Value & Cost Centre 2 are in Sheet 2.

As can be seen, there is also a data filter.

The requirement. Whenever the user selects one of the cost centre on sheet 1, the total (column, Value) must be updated under the column Document Value on Sheet 2 against the Cost Centre 2.

For eg., if the user selects Langen, the total value for Langen is -737036.21. This must be updated in sheet 2 under Document Value against Cost Centre 2

Eg 2., if the user selects Mannheim, the total value for Mannheim is 1222835.76. This must be updated in sheet 2 under the column Document Value against Cost Centre 2.

I have also given the same data in Text format.

NOTE:- Even Excel does not dynamically update the total for each cost centre that is selected randomly. I am not sure how to accomplish this task in Alteryx. Kindly help.

Cost Center	Name	Value				Document Value	Cost Centre 2
Langen	Deliver	-1012.13				-737036.21	Langen
Langen	Deliver	1012.13
Langen	Legal	292783
Langen	Legal	-1029819.21
Mannheim	Legal	1180406.21				1222835.76	Mannheim
Mannheim	Legal	11165.67
Mannheim	Site	7443.78
Mannheim	Site	23820.1
Hyderabad	Site	270994				388107	Hyderabad
Hyderabad	Site	117113
Bangalore	Site	245.93				11581.84	Bangalore
Bangalore	Sales	72.09
Bangalore	Sales	11263.82
Munich	Sales	21399.29				55283.29	Munich
Munich	Sales	33884

[OTrieger]

@Kallis 

There are several ways on how to do it, one of them will be to have all the Cost Centre connected to a List Box tool in the User Interface menu, then get the automation sum up the totals for each of the selected cost centers and then write it to the other sheet.

[Kallis]

Hi @OTrieger 

Let me try this but is there any other way? 

Regards

Bobji

[OTrieger]

@Kallis 
Well, as long as you want that it will be based on the user selection, you will need to use User Interface option. You can use any of the user Interface tools for that purpose, it can be a List Box, Drop Down, Text Box you can select any one of them. Each have advantages and disadvantages.

You can create an automation that by default will create all the totals and then the user can delete those that he does not needs. 

As long as the requirement is that the user will be able to select the cost centre, you best choice is to go with the User Interface, as that is exactly what is the purpose of it.

[Kallis]

@OTrieger 

I have not used any of these tools but when I saw some videos, it seems that the selection happens within the Alteryx application itself. If that is the case, the end user does not have Alteryx installed on their machine. They only work on the Excel that is generated. Kindly let me know.

[Kallis]

@OTrieger 

Would you mind sending a sample WF with the data above?

[OTrieger]

@Kallis 

Does the users have access to Alteryx Gallery, a server where the automation is saved and run it as analytic app?

Because if not then you last option is to set it based on excel file given from the user for his preference, but in this case it will be better to create totals for each of the cost centres and then the user can delete what he does not need from the excel output file.

[Kallis]

@OTrieger 

The end users do not have access to any version of Alteryx. They are all pure financial people and lack technical knowledge. 

I think that they have asked for a requirement that even Excel cannot fulfil. I mean, in Excel, if you SUM a list of values for a specific cost centre and change the cost centre, that cell will not reflect the SUM for the new cost centre. 

With your permission, shall I ask for support from the others?

[OTrieger]

@Kallis 
First this is an open discussion anyone is welcome to suggest his solution.

If they do not have any access to Alteryx, then how they could select the cost centre? Are you looking for a solution in excel or Alteryx?

Why don't you create the sum to all cost centres and then they can keep what they need?

[Kallis]

@OTrieger 

Yes, that is exactly what I am going to do. First, I wanted an Excel solution in Alteryx. Now, I will give them what is possible.

Thanks for your help though.