Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

Excel trick for reading tables? [Closed]

Report
-
 SKent -
Hello,


I'm using Microsoft Excel. Imagine I have a table with two columns, one labelled "Staff Name" and one labelled "Able to lock-up."

The first column consists of names such as Tom, Dick, Harry etc. The second columns is simply like Yes, No, No, Yes, Yes etc denoting whether the respective staff name can lock-up.

That's that. Now I want to create a third, separate list, that simply lists the staff that are able to do lock-up. This is to use in like a separate rota worksheet via the data validation tool so that it only lets the me (the end-user) to only select staff able to do lock-up. I'm assuming creating the third table would be the solution, or perhaps Data Validation tool is versatile for this via custom?

Anyway, I was playing around, creating a third list using the function in a later column but same row as the staff name = if(C1="YES",B1,"") so that if they are able to lock-up it displays the staff name and if not, leave it blank. This works, but the data validation in-cell drop box includes the blanks cells and looks ugly, and I don't like ugly things.

Any suggestions?

1 reply

Answer 1 / 1

Quick and easy way is to use Pivot Table.

Before you start, create a range for your data

Then:

Look on the Data menu, select Pivot Tables. The Wizard will step you through the process.

Select the columns you want displayed, as well as the "Section" column, in your example "Able to Lock Up."

After you have the Pivot Table created with the wizard, a few more steps to clean it up.

such as setting rows to not total.

Then you can select the column title for "Able to Lock Up" ans select "Yes" The table will only sow "Yes"
Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!