Jump to content


Photo

Adjusting formulas when sorting columns


  • Please log in to reply
1 reply to this topic

#1 Theo_Fr

Theo_Fr

    Newbie

  • Members
  • Pip
  • 1 posts

Posted 11 July 2017 - 03:08 PM

Hello, 

 

I also have a question regarding sorting spreadsheets.

My issue has to do with sorting a range by columns (see Expert Skills, Lesson 1-15).

My workbook has seven columns, of which column G displays the product of values in columns E*F.  I added a dummy row (row 1) above the range, where I put some numbers, in an order different from that in the video. In my version, columns E, F and G will not be adjacent anymore. When I try to sort by row 1 from values smallest to largest, I get a #VALUE error in the column that was supposed to display the results of columns E*F. This is understandable, as now these two column contain text. But I am wondering if there is a way that the formulas can be updated when sorting by columns?

Please see my workbook attached. 

 

Thank you! 

 

Theo

Attached Files



#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 831 posts

Posted 12 July 2017 - 01:13 PM

Hi Theo,

 

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:

=MATCH("In Stock",$A$2:$G$2,0)

 

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:

=ADDRESS(ROW(),MATCH("In Stock",$A$2:$G$2,0))

 

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:

=INDIRECT(ADDRESS(ROW(),MATCH("In Stock",$A$2:$G$2,0)))

 

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:

 

Attached File  Inventory-2.xlsx   14.48KB   4 downloads

Inventory-2.png

 

I hope this is the answer you were looking for, but please feel free to reply if you need any more help with this.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users