Building a table from a list on another worksheet

[Thomas Dziuk]

I am trying to build a visual reference table to represent a list of services
available at different sites. I can grab from our database a list with three
fields that include a Unique Number, Service Name, and Site. What I am trying
to do is make sure that all the combinations we need in the system. So I am
trying to build a table that will show for each Service/Location combination
we are expecting that there is an entry in the system for it.

What I need to do Is make this:
Service Site
Name NE E SE
Eating 1 4
Swimming 2 3

From This:
1 Eating NE
2 Swimming NE
3 Swimming E
4 Eating SE

I have played around with different functions but I can't get the correct
number to show up in the table from the list instead for example it will show
rule number 1 in the table for Eating at SE because that is where Eating
first shows up.

 

[Conan Kelly]

Thomas Dziuk,

It looks like Pivot tables would be perfect for what you are trying to
accomplish.

In help (or google), type "Pivot Tables" and learn every thing you can from
them.

For XL 2003 (not familiar with XL 2007):
- Place your currsor somewhere in your list data.
- "Data" menu > "PivotTable and PivotChart Report..." sub-menu
- If you want, you can step through each step and read everything w/o making
any changes...or you can just click "Finish" button right from the start.
- From the "PivotTable Field List" window/pane/what-have-you:
- Drag the "Service" column to the "Drop Row Fields Here" section.
- Drag the "Site" column to the "Drop Column Fields Here" section.
- Drag your numbered column to the "Drop Data Items Here" section.

HTH,

Conan

 

[Thomas Dziuk]

That works except for one thing, not all sites have services listed for them,
we are moving to having one entry per service/location in the system, but for
now we just had a Service and if it was offered multiple places we didn't
give it a location, so some of my locations dont show up on they pivot yet,
but I want to be able to track which combinations are still needed to be
filled it.

 

[Conan Kelly]

Thomas,

I don't quite understand what you are saying.

If you followed my directions, your pivot table should be just like the
example in your original post.

You should have a row for every unique service in your list. You should
also have a column for every unique location in your list. Then the data in
the table will show you which Sevice/Location combinations you have, and
which ones you need. Every where you have a number where a service and a
location interescts, you have at least one entry for that combination.
Everywhere you have a blank in the intersection of service & location, you
don't have an entry...thus you need to create one.

...not all sites have services listed for them, ...
..., but for
now we just had a Service and if it was offered multiple places we didn't
give it a location,

So you should have something like "(Blank)" in you Locations columns with
services listed. For each service that shows up in the "(Blank)" Location,
you need to create a record/row in your list for each Location that shows up
in your pivot table.

...so some of my locations dont show up on they pivot yet,

Make sure each location shows up in your list at least once. Don't even
worry about matching a service with it. Just add rows to your list (for
each missing location) with the Service column blank. Then refresh your
pivot table (FYI: pivot tables don't update automatically when data is
changed in the list...right-click the pivot table > click "Refresh
Data"...or click the "Refresh Data" button on the "Pivot Table" toolbar).
Then you'll have every location listed, and every service listed. Where
ever there is a blank, you need to create an entry for that Service/Location
combination.

By-the-by, when you add new records/rows to your list, unless you are using
a Dynamic Named Range for your list (and refered to it in the creation of
your pivot table in the wizard), you will have to manually adjust the range
that your pivot table refers to (right-click the pivot table > "PivotTable
Wizard" > Step back through the wizard where the range is defined > redefine
the range to include new rows > click finish)...depending on how you add new
records to your list. If you just go down to the very last entry and start
adding new records after the last one, the range for your pivot table won't
adjust. If you insert blank rows/cells somewhere between your first and
last record (will mess up your sorting if it is sorted...just re-sort
after), then type in your data in these new blank inserted rows. The range
should adjust automatically...all you need to do is refresh the pivot table.

HTH,

Conan