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?

 

[Richard Schollar]

Hi Frankie

It's very difficult to reach any conclusions when we don't know what formulas you are using. How is your filtering achieved - is it just standard Autofilter?

 

[GMorris]

It's all gonna depend on whether you are just trying to keep the second sheet static, or if you only want it to change at certain events?

 

[frankieo07]

I do want the second sheet to change when I change data in the first. As for the formulas I'm using, they are just simple weighted averages. So a random cell on spreadsheet 2 might have formula: ('spreadsheet'!A1*'spreadsheet'!A2+'spreadsheet'!B1*'spreadsheet'!B2)/('spreadsheet'!B1+'spreadsheet'!B2)

Hope that's helpfull

 

[frankieo07]

and yes the filtering is just auto filter

 

[GMorris]

Ah, so maybe I didn't get it the first time. Are you saying that the numbers on sheet two are NOT changing, and you DO want them to?

 

[frankieo07]

Not exactly. I want them to change when I alter the numbers in spreadsheet 1, but not when I change the order of my filter (as in sort descending instead of sort ascending).

 

[Richard Schollar]

I think you're going to be out of luck because you are physically changing your first sheet when you change the Sort order. As ever I would love to be proved wrong...

 

[frankieo07]

Can anyone then think of a way to get around this. A lookup table or something of the sort?

 

[GMorris]

Yeah, what he said.. Basically, the only workaround I can think of would be to implement some sort of VBA solution that would allow updates to the second sheet when appropriate, but not change the order based on the sorting of the first. I have no doubt it can be easily done with maybe even a UDF, but off-hand I don't have anything floating around!

And that's just one possibility. You may also (probably using code) disable automatic formula updating for certain periods of time. Just a couple of thing you might think about if you really need this.