Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Building a table from a list on another worksheet
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Building a table from a list on another worksheet
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" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Building a table from a list on another worksheet
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" wrote: 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" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Building a table from a list on another worksheet
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 "Thomas Dziuk" wrote in message ... 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" wrote: 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" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Building a rank table using If | Excel Discussion (Misc queries) | |||
building list from a column of data | Excel Worksheet Functions | |||
building list | Excel Worksheet Functions | |||
Building list based on condition | Excel Worksheet Functions | |||
self-building dropdown list | Excel Worksheet Functions |