Create dynamic columns depending on a checkbox - excel 2007

Question

I have a simple table with 4 values and a checkbox for each of them:

Sellers: | John | Antonio | Paulo | Carlos

On another excel sheet, I have a table where I can add rows, and where each row has Month-Year. I’m wanting the columns to be the sellers with the active checkbox of the table. If you select John and Paul, my table will only show sales to these two sellers. If you remove João’s checkbox, there is only one column. If you put checkbox in all, my table will have four columns.

What is the best way to do this in excel 2007?

Answer 1

I can do something similar to what you asked, let’s see if you answer.

Assuming the following table:

+------------+---------+
| Vendedores | Mês-Ano |
+------------+---------+
| João       | jan/14  |
+------------+---------+
| João       | fev/14  |
+------------+---------+
| Antonio    | jan/14  |
+------------+---------+
| Antonio    | fev/14  |
+------------+---------+
| Paulo      | jan/14  |
+------------+---------+
| Paulo      | fev/14  |
+------------+---------+
| Carlos     | jan/14  |
+------------+---------+
| Carlos     | fev/14  |
+------------+---------+

Click on the Insert tab and then click on Dynamic Table:

It will open a window asking for the ranges of data to be used in the creation of this dynamic table, usually Excel itself already automatically selects the entire table, if you have not selected mark the range of data you want to use. Click on Ok.

A new tab will be created. Drag the seller field to Column Labels and drag the Month-Year to Line Labels, as shown below:

A table like this will be created:

Notice the button with a arrow on the side of the Column Labels cell, if you click it you can choose which sellers you want to show in the dynamic table:

Finally, after choosing the sellers, copy the table and Paste As Value in some new spreadsheet, because the dynamic table is read-only.