Summary of Inventory Values - Sorting is failing

[bobgrand]

Hello all,
I am using the following formula that this group was helpful with.

-------| Column A------| Col B-------------| Col C-----------| Col D-----------| Col E
Row 1 | Vendor Code--| Item--------------|Cost Each------| Qty on Hand---| Value on hand
Row 2 | Vend 1---------| Item 1-----------| $5--------------| 50---------------| $250
Row 3 | Vend 1---------| Item 1-----------| $10-------------| 80---------------| $800
Row 4 | Vend 1---------| Item 1-----------| $5--------------| 50---------------| $250
Row 5 | Vend 1---------| Item 1-----------| $20-------------| 10---------------| $200
Row 6 | Vend 1---------| Item 1-----------| $15-------------| 40---------------| $600
Row 7 | Vend 1---------| Item 1-----------| $5--------------| 50---------------| $250


Then in Sheet 2 you would have


--------| Column A------| Col B
Row 1 | Vendor Code--| Value on hand
Row 2 | Vend 1---------| =sumif(Sheet1$A$2:$A$7,Sheet2$A2,Sheet1$E$2:$E$7)
Row 3 | Vend 2---------| =sumif(Sheet1$A$2:$A$7,Sheet2$A3,Sheet1$E$2:$E$7)
Row 4 | Vend 3---------| =sumif(Sheet1$A$2:$A$7,Sheet2$A4,Sheet1$E$2:$E$7)

In Jan-TTLs sheet and in Feb-TTLs sheet I have all the raw data needed. In a Summary TTLs sheet I have 4 columns shown below,

-------COLA----|COL B-----|COL C----|COL D
-------VENDOR--|JAN ------|FEB -----|DIFF
ROW 1--ABC123--|-4254.46--|4418.29--|---163.83
ROW 2--THEWEG--|---74.02--|--95.72--|----21.70
ROW 3--BAKPOW--|-3885.00--|2310.00--|(1575.00)
ROW 4--WAVNET--|10001.70--|19175.14-|--9173.40

When I sort on column D High to low it does not put the 9143.40 at the top.

Any help with this would be greatly appreciated.

Thanks
Bob

 

[WassimN]

Hello bobgrand

Hum!! test to see if this will work. Put a 1 in an empty cell. Copy that cell. Highlight the range D1:D4 in Column D and on the Home Tab click the arrow below Paste to display the Paste Options, and choose Paste Special at the bottom. From the Paste Special options choose Multiply under Operations, a bit above the OK buttom. Now this should convert any cell entry that looks like a number it numbers. Now try the sort again.

Also, you can use the way the entries are displayed in the cells. A number left justified is a text entry, because true numbers are right justified. This is a general rule, cell formatting can make this invalid.

Hope this helps.