Hello.
I need to calculate a sum from different prices and data that are both kept in different tables. When I try to do so by adding field a * field b and so on, I get an automation I cannot understand and cannot correct.
I wrote in field G2 in the main sheet the calculation I need to do (without = to avoid getting a formula).
What am I doing wrong?
Thanks
Hi Bruno,
Your data in the main tab isn't in a Tabular format. You need to unpivot columns D:F, then you can merge the tables to bring in the price from the list sheet and perform the multiplication.
See file attached.
Mynda
Hi Mynda, many thanks for your quick answer.
When I worked the correct way (tabular) I didn’t find a way to fill the table easily like with a form.
The way I wrote the table, I can open a form and easily insert for each name the order by typing only the number of attributes (1 for lemon, 2 for apple etc..)
Is there another way to fill these orders while working in tabular ?
Hi Bruno,
You can keep your layout for data entry and then use Power Query to create the unpivoted layout of the data containing your calculations. A click of Refresh All on the data tab of the ribbon will update all of the queries/calculations.
If you want the data entry table to automatically calculate the price in column G, then you'd need multiple INDEX & MATCH formulas to lookup the price from the List. e.g.:
=INDEX(Prices[[Product]:[Price]],MATCH(Table2[[#Headers],[apple]],Prices[Product],0),2)*Table2[@apple] +INDEX(Prices[[Product]:[Price]],MATCH(Table2[[#Headers],[lemon]],Prices[Product],0),2)*Table2[@lemon] +INDEX(Prices[[Product]:[Price]],MATCH(Table2[[#Headers],[pear]],Prices[Product],0),2)*Table2[@pear]
Mynda
Many thanks!
