Auto filter messing up my data

[lochem]

Help!
when i autofilter any column in my database it rearranges all rows -
but i have formulas in one column that are dependent on other cells adjacent to it in a row, and that relative formula doesnt move with the cell!

so when C3 at first = B3/A3 (let's say result in C3 is 0.32)
after i filter D by AtoZ (for example), all data in rows in B and A move around relative to the filter.. and so now i get a different value in C3 because the B3 and A3 have changed!

am i supposed to have made the cell references relative? locked??
if i have to change the cell references is there anyway to do this for an entire column or selected range?

 

[Sahak]

Hi Lochem

What do you mean

after i filter D by AtoZ

Do you mean Sort D by A to Z?

 

[lochem]

Hi Lochem

What do you mean
Do you mean Sort D by A to Z?

i mean Sort column D alphabetically, either by sort or by the filter doing the sort, either way
any ideas?

 

[xenou]

Generally filtering and sorting are two different things - I don't use XL2007 most of the time but I think probably there is a sort option available to you when you right click the filtered column (?). We don't see this in XL2003. Anyway, you really should be either sorting all the data together - Columns A,B,C, and D. Which is another way of saying each row should stay together no matter what you do. That's typically how this works. Possibly you've only filtered one column (?). Try filtering all the columns so they form complete "dataset" that Excel can keep track of.

 

[lochem]

yes, there is a choice to sort alphabetically when selecting the filter of a column. im pretty sure tho, that when i selected entire database it still did this...

 

[xenou]

Is this one of those fancy schmancy database thingamajiggys?

 

[Norie]

Are you actually sorting the correct range?

 

[lochem]

hm, im not sure what youre referring to. my database is not so fancy, :P
and the operation isnt really either. its just sorting alphabetically. it sorts everything relative to the sorted column, so its doing its job. but the problem is, (i wonder if i can explain this in a different way):
the cell is retaining its own formula. so when the rows move around when the sort is done,
C3 still has the formula =B3/A3 which is now resulting in a different value (after sorting) because A3 and B3 have different information in them now!

 

[lochem]

maybe ill take a before and after screenshot, before sort and after

 

[lochem]

OK
i analyzed my formulas throughout the database and this is what i think is happening:
in one of the columns, there is a VLOOKUP function used to calculate the commission earned on the sale based on cell references that are not set up properly -
this is almost 100% the problem.
please have a look (this is one cell's formula when the database is fine, before the sort):

=VLOOKUP(H3,Appendix!$F$18:$G$22,2,0)*(F3-D3)

where H3 is the name of an employee to be found by VLOOKUP in the range in column 1 in Appendix F18:G22 and in column 2 of that range i have the commission rate. that value (example: 0.125) gets multiplied by (F3-D3) which is the price at sale minus the purchase price of the item. the result is the commission earned by the employee on the sale.
something is wrong here and its causing everything else to get miscalculated...