Autofilter with formulas

[frankieo07]

I have one basic spreadsheet with all the data and then I filled a second spreadsheet with weighted averages based on the data in spreadsheet 1. However, then when I switch my filter on spreadsheet 1 all of the numbers change in spreadsheet 2. Is there some way to avoid this?

 

[frankieo07]

Just to get some closure on the situation (because I know you all desperately wanted to know the answer) I finally figured it out. By giving an arbitrary, unique number value to each row of spreadsheet 1 (i.e. putting a column in at the beginning with cells 1, 2, 3, 4, ...) and then using vlookup on spreadsheet 2, the values no longer change when I filter my answers differently and they do change when values are changed on spreadsheet 1...so there you have it

 

[GMorris]

Ahh, well I'm happy you've found your answer! It might also interest you to know that you've actually applied a fundamental theory of database application. You should think about that if the application is large enough, it might be time to move to Access or even some OTHER database program!

 

[whiskycan]

Would a Pivot table be a better solution?

 

[frankieo07]

probably so because the way I did it took rather a lot of effort, but I'm already done