display a calculated value from excel back to powerapps

[GrantAstley]

Just wondering is there a way to update a table in excel and have the vba assosiated with that table be exicuted?

It looks like powerapps edits online which prevents and vba and macros from running.  Is there a way to force it to open an instance of excel or a way to trigger a vba event from power apps?

As background I have a table which gets written to and read from by powerapps.  The read from values need to be calulated based on the inputs in that table.  Siince powerapps wont read or write to a table with formula in it, I have a vba worksheets_change script that runs whenever 1 of the columns is updated.  This then inserts the result in the correct column ready to be read by powerapps.  However since this is not exicuted when editing via powerapps the result is not updated.

Is there a workaround to what I am trying to achieve (displaying a calulated value from a table in excel back to powerapps)?

[Drrickryp]

Hi @GrantAstley,

Can you do the calculation in Powerapps using the data from the table and the functions in Powerapps?

[GrantAstley]

Not sure on what can be calulated in powerapps to be truthfull.  

I need to be able to count the number of rows in column B that have the same value as what I am going to enter - Currently using 

=IF(B7>0,COUNTIF(B$7:B7,B7),"")

placed in column F

I have to be able to use vlookup as follows

=IF(ISERROR(VLOOKUP(B$7:B$1000&"_"&F$7:F$1000-1,G$7: O$1000,8,FALSE)),C$7:C$1000,VLOOKUP(B$7:B$1000&"_"&F$7:F$1000-1,G$7: O$1000,8,FALSE))

Placed in column M, not sure why but : O in the code box is showing up as an emoticon! Shouldt the code box not convert text to emoticons?  So I left a space between : and O that would not normally be there.

Then the rest is just addition and subtraction which I would have no problem doing.

Do you know if the above type calcs are possible within powerapps?  

[Drrickryp]

Hi @GrantAstley,

I'm no expert in Excel but I think there are analogous functions in Powerapps to do what you want.  

To get the number of rows that match a value see if this works. 

Startiing with an Excel table named Table1 with columns headings, col1,col2,col3,col4,etc.  To get the number of rows in the table matching a particular value in one of the columns, say col2, put a button, a TextInput control and a label on the screen. 

Create a filtered collection of your table by putting a value in the TextInput control, set the OnSelect property of the button to ClearCollect(colTable,Filter(Table1,col2=TextInput1.Text)) . Make the Text property of the label, CountRows(colTable).  When you press the button, the label will show the number of rows in your filtered table. You can then reference this value in your formula later on using Value(Label1.Text).  This converts the Text in Label1 to a number.

The second formula is a bit trickier to me as I have rarely used formulas in Excel. As I understand it, Vlookup looks up a value in the first column of the specified range, and returns a value in the same row from another column.  This seems to be similar to the Lookup() function in Powerapps which will search a datasource or collection and returns the first record in a table that satisfies a formula. An optional parameter can reduce the result to a single value in that record. The syntax is LookUp( Table, Formula [, ReductionFormula ] ) or in the case of the example above, LookUp( colTable, col2=Textinput1.Text, col3).  The result of the lookup without the optional reduction can be displayed as a row in a Datatable control, or with the reduction, it can be displayed as a single value in a label.  I'm not sure if this helps get you on the right track but it seems to me that the required functions are present in Powerapps to accomplish your goal. 

[GrantAstley]

Hi 

Yes that looks like it should work.  I will have a play around and see if I can get it to work using that approach.  The only thing I would have to check is if the lookup function can get a value from a previos row.  It will look for the value in column L on that row in cloumn G then return the value in the row that the value was found in column G in column N of that row

ie in the below.  The formula is is the green highlighted cell.  It looks for the value 1_2 which is in column 10 in column6 of the table and returns the value in column 12 of the table in the same row of which the value was found - orange highlighted cell.

[Drrickryp]

Hi Grant,

Does your table contain an Index column like an ID or list of sequential numbers?

[Keyvan]

@GrantAstley Hello, 
 I have the same issue and can not do the calculation in Powerapss since the calculation is complicated. 

Did you find the solution for that?

Thank you

Keyvan