I have the following problem!
I have one table with some data in it. At the top of each column, there is a Yes or No droplist cell. All the items in the Table underneath the Yes/No cells can be updated from a second workbook via a macro, and so the data in each column can change. The Table in the current workbook can also have its columns sorted via another macro in the current workbook. Hence the column headers in the Table are changing continuously and the data is not static.
I want the Yes/No cell to blank all rows beneath it, let's say for that for Column B. The Yes/No cell is cell B1, and I want to blank all the data in cells B2:B20 if the Yes/No cell is set to No. And after then having sorted the table, I want to be able to click the Yes/No cell and set it to Yes (the Yes/No cell is also sorted and may be in for example D1 now) and have the existing data show up.
As an example, let's say I update the data in the table via the existing macro, then click No on the current data in Cell B1 so B2:B20 go blank, and then I sort it according to a parameter in the rows. The sorter will also move the relevant Yes/No cells. So the same Yes/No cell corresponding to the existing data could, for example, now be in Cell D1 and the data in B2:B20 would be completely different. I would want to click set No in Cell D1 and have the existing data that used to be in B2:B20 show up in D2:D20.
The only way I thought I could do this is if I had two separate tables (one for the Yes/No, and one as a reference) , the reference table is updated via the existing macro and the data is copied to the Yes/No table, and both are sorted when the Yes/No table is sorted. Except the second (reference) table does not have any Yes/No function, so whilst all the columns are sorted, the second reference table still contains all the data in the relevant columns. I want to be able to link clicking No to that data, which may have been sorted, in the reference table.
I expect there would be a duplicate macro for each Target Yes/No cell in each column with the cell references altered between each. If anybody can help that would be wonderful.
I'm also wondering if the method could be adopted if the columns in the first table could, in addition to the above options, be manually arranged (via shift+click). I think the method for the above would break down as the columns in the reference table wouldn't move at all. Is there any way to get it working with this additional requirement?