I created a spreadsheet that had several hundred rows of data. There were about 10 columns of independent variables and about another 20 columns that generated interim and final results (dependent variables) based on the 10 columns for that row.
I then wanted to do some filtering so I could look at results for rows that had specific values of the independent variables. However, I wanted to do this as an advanced filter so that I could save the results of the filter to another location. This way, I could look at different filtered sets of the data at one time.
Still no problem. Defining a separate location and the desired values for the variables just required using the Advanced Filter correctly. So we defined 10-12 different ways of looking at the data with the different filters.
Then we found an error in the equations for generating the 20 columns (dependent variables). Correcting the data set was easy. Correcting the filtered results was not!
When Excel created the Advanced Filter in another location, it was like a Paste Value operation – the filtered info was no longer tied to the data set.
Any ideas on how to tie the filters back to the original data set using Excel? (An Access or hybrid Excel-Access solution seemed to work but we were looking for an all-Excel solution.)
