Loop through Filtered Data (visible rows), to derive calculated value from 2 columns.

[inddon]

The macro with .UsedRange.Name = "SceData" is the old macro and it does something different.
I attach file with my PromoteHeaders amendment to cater for older Power Query versions.

Thank you p45cal, it works good.

I haven't tried Power Query, so no idea how this would work. It is asking a bit more from you. If possible and time permits, could you mention in steps how did you do this?

Regards,
Don

 

[p45cal]

Oh groan, I hate this.

There is a named range SceData on the Lines sheet which is updated/added by the first 3 lines of the blah macro.
The behaviour of Power Query varies depending on whether the selected cell/selection is in a named range or not when you start the following.
Select a cell in the table on the Lines sheet.
Go to the Data tab of the ribbon and click From Table/Range in the Get & Transform Data section of that tab:


It should do 3 steps for you.
Then choose the Add Column tab and choose Custom Column:


which will give this, which you have to fill in. Update the dialogue which pops up as follows:

and click OK.

Select the Section column, go to the Transform tab, and click on Group By, follow the annotations:


Then go to the Home tab, click the small Close & Load dropdown arrow and choose Close & Load to…

continued in next message…

 

[p45cal]

and choose say the following, selecting any cell where you want the table to appear:


and that's it.

At the start, if there is no named range, Power Query asks you to confirm the range and that it has headers:


it will create an Excel table and there is no Promoted Headers step in the query:


otherwise there's no other difference.

 

[inddon]

and choose say the following, selecting any cell where you want the table to appear:

Thank you very much p45cal

Regards,
Don