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?

 

[xenou]

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!

This is a bother sorting out. Normally, A3 and B3 should "sort" or "move" with C3 - the whole row should sort together. This suggests either this is not happening (by design or otherwise), or that the values in A3 and B3 are also dependent on something else that changing because of the sort.

I'm not sure I see how it all fits ... you might need to post a sample row, with all the formulas or values in that row shown (perhaps before and after). The vlookup might be part of it as you suspect, but its hard to see how that fits in with anything in the other columns, or column C.

 

[ZVI]

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)

Are H3,F3,D3 the cells of Appendix sheet?

 

[lochem]

No. H3, F3, D3 are on this sheet where i have the database. the only reference to another sheet is the range you see from Appendix.

 

[ZVI]

Ok, just a thought - in sorting range formulas remove all referencing to its own sheet if present.
See example in Sorting doesn't sort

 

[lochem]

Wow. big props. I really dont know what to say - that's probably the most frustrating thing i've been dealing with and now it's solved! thank you thank you!
there has to be an explanation to this though - just by fixing the cells that had the same-sheet self-reference in the formula... how did that change things?
it worked, but WHY did it work??

 

[ZVI]

Lochem,
I don’t know why it works so, just has found this feature in the past.
Happy it has helped,
Vladimir

 

[lochem]

well, my observation is that cell referencing to an already active worksheet takes away the opportunity for the cell formula to move relative to the sort.
seems like the cell reference becomes more than just a reference, rather a defined pointer to a specific cell address.
what do you think?

 

[ZVI]

well, my observation is that cell referencing to an already active worksheet takes away the opportunity for the cell formula to move relative to the sort.
seems like the cell reference becomes more than just a reference, rather a defined pointer to a specific cell address.
what do you think?

Agree, seems that cell’s formula referenced to its parent worksheet like =Sheet1!A1 is recognized as external reference and doesn’t change at sorting. Instead, the reference like =A1 is modifiable at sorting.