I have looked into this and couldn't find an easy solution. Unfortunately it looks like Excel doesn't automatically adjust formulas when sorting by columns and that it won't allow you to sort by columns after placing the data into a Table.
The only solution I can come up with requires you to use some very complex formulas, so you may prefer to simply apply the formulas after sorting the source data. Here is the formula I used:
After sorting, you cannot be sure which column contains the "In Stock" and "Unit Price" data, so you must use the MATCH function to locate the correct column:
This function searches the header row (cells A2-G2) and returns the column number of the "In Stock" column.
The number of the column alone isn't much use to you - you want to get the address of the cell that contains the actual values. You can do that by using the ADDRESS function:
This will return a cell reference - $C$3 for the first row of data. The ROW() function simply returns the number of the current row when used in this way.
The cell reference returned by the ADDRESS function will be treated as text by default, so you will need to use the INDIRECT function to extract the value inside the cell:
For a complete explanation of the INDIRECT function, see the video lesson: Expert Skills Lesson 4-5 Use intersection range names and the INDIRECT function.
The same formula can be used to extract the Unit Price values, so your final formula will look like this (although you could split this into several columns):
=INDIRECT(ADDRESS(ROW(),MATCH("In Stock",$A$2:$G$2,0))) * INDIRECT(ADDRESS(ROW(),MATCH("Unit Price",$A$2:$G$2,0)))
This formula will work in any column so it will be unaffected if you rearrange the columns by sorting. As I said, however, you may prefer to avoid this complex approach.
I have attached a copy of the workbook with the formulas applied:
I hope this is the answer you were looking for, but please feel free to reply if you need any more help with this.